在json_agg中添加distinct

Question

我有以下示例数据：

--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');

查询：

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;  

输出：

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

预期输出：

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

这就是聚合函数中DISTINCT关键字的作用：

json_agg (DISTINCT
jsonb_build_object (...)
)

然后重复的条目将被删除。

注意：必须使用jsonb_build_object而不是json_build_object，因为json没有相等运算符。

Answer 2

json部分与此无关。

此外，您的输出与您在EmpAdd部分中添加的内容不匹配。

将ea.address添加到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;