Add distinct within json_agg

Question

I have the following sample data:

--EmpMap:

create table EmpMap 
(
   id int
);

insert into EmpMap values(1),(2),(3);

--EmpInfo:

create table EmpInfo
(
    empid int,
    empname varchar
);

insert into empinfo values(1,'Mak'),(2,'Jack'),(3,'John');

--EmpAdd:

create table EmpAdd
(
   EmpID int,
   Address varchar
);

insert into EmpAdd values(1,'Addr1'),(1,'Addr1'),(1,'Addr1'),(2,'Add2'),(3,'Add3'),(2,'Add2');

Query:

select e.ID,
       count(1) as Counts,
       json_agg
       (
            json_build_object
            (
            'EmpID',ei.EmpID,
            'EmpAdd',ea.address
            )
       ) as emp_json_address
from empmap e
join EmpInfo ei on e.id = ei.empid
join empadd ea on ei.empid = ea.empid
group by e.ID;  

Output:

id|counts|emp_json_address                                                                                         |
--|------|---------------------------------------------------------------------------------------------------------|
 1|     3|[{"EmpID" : 1, "EmpAdd" : "Addr1"}, {"EmpID" : 1, "EmpAdd" : "Addr1"}, {"EmpID" : 1, "EmpAdd" : "Addr1"}]|
 2|     2|[{"EmpID" : 2, "EmpAdd" : "Add2"}, {"EmpID" : 2, "EmpAdd" : "Add2"}]                                     |
 3|     2|[{"EmpID" : 3, "EmpAdd" : "Address3"}, {"EmpID" : 3, "EmpAdd" : "Add3"}]                                                                       |

Expected Output:

id|counts|emp_json_address                                                              |
--|------|------------------------------------------------------------------------------|
 1|     1|[{"EmpID" : 1, "EmpAdd" : "Addr1"}]                                           |
 2|     1|[{"EmpID" : 2, "EmpAdd" : "Add2"}]                                            |
 3|     2|[[{"EmpID" : 3, "EmpAdd" : "Address3"}, {"EmpID" : 3, "EmpAdd" : "Add3"}]     |

Answer 1

That's what the DISTINCT keyword in aggregate functions is for:

json_agg (DISTINCT
   jsonb_build_object (...)
)

Then duplicate entries will be removed.

Note: you have to use jsonb_build_object rather than json_build_object, because the is no equality operator for json.