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"}]     |

Laurenz Albe · Accepted Answer · 2020-07-15T15:12:49.063

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.

edited Jul 15 '20 at 15:12

answered Jul 15 '20 at 15:06

Laurenz Albe

209,280
17
206
263

I don't think `DISTINCT` work with `json_build_object`. – MAK Jul 15 '20 at 15:07
Getting an error: `SQL Error [42883]: ERROR: could not identify an equality operator for type json` – MAK Jul 15 '20 at 15:11
Oops, you were right, fixed. You have to use `jsonb_build_object`. – Laurenz Albe Jul 15 '20 at 15:13

Mike Organek · Answer 2 · 2020-07-15T15:53:26.320

The json part has nothing to do with this.

Also, your outputs do not match what you added in the EmpAdd part.

Add ea.address to the group by.

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, ea.address;

Add distinct within json_agg

2 Answers2