最便宜和最简单的“DISTINCT”操作是……不要在第一次“代理交叉连接”中增加行。先聚合,然后再连接。参见:
最适用于返回少量选定行
假设您实际上不想检索整个表,而只想一次检索一个或几个选定的照片,并带有聚合详细信息。那么
LATERAL
子查询是快速而优雅的解决方案:
SELECT *
FROM photo p
CROSS JOIN LATERAL (
SELECT json_agg(c) AS comments
FROM comment c
WHERE photo_id = p.photo_id
) c1
CROSS JOIN LATERAL (
SELECT json_agg(t) AS tags
FROM photo_tag pt
JOIN tag t USING (tag_id)
WHERE pt.photo_id = p.photo_id
) t
WHERE p.photo_id = 2;
这将从评论和标签中返回整行,并分别聚合到JSON数组中。行不像您的尝试中那样重复,但它们只能在基本表中“区分”。
要在基本数据中另外折叠重复项,请参见下文。
注:
LATERAL
和json_agg()
需要Postgres 9.3或更高版本。
json_agg(c)
是json_agg(c.*)
的缩写。
我们不需要进行LEFT JOIN
,因为诸如json_agg()
的聚合函数始终返回一行。
通常情况下,您只需要至少排除冗余的photo_id
列的子集:
SELECT *
FROM photo p
CROSS JOIN LATERAL (
SELECT json_agg(json_build_object('comment_id', comment_id
, 'comment', comment)) AS comments
FROM comment
WHERE photo_id = p.photo_id
) c
CROSS JOIN LATERAL (
SELECT json_agg(t) AS tags
FROM photo_tag pt
JOIN tag t USING (tag_id)
WHERE pt.photo_id = p.photo_id
) t
WHERE p.photo_id = 2;
json_build_object()
是在Postgres 9.4中引入的。在旧版本中如果使用ROW
构造函数就不会保留列名,因此变得繁琐。但有通用的解决方法:
此外,还可以自由选择JSON键名,不必坚持列名。
最适合返回所有或大多数行
SELECT p.*
, COALESCE(c1.comments, '[]') AS comments
, COALESCE(t.tags, '[]') AS tags
FROM photo p
LEFT JOIN (
SELECT photo_id
, json_agg(json_build_object('comment_id', comment_id
, 'comment', comment)) AS comments
FROM comment c
GROUP BY 1
) c1 USING (photo_id)
LEFT JOIN LATERAL (
SELECT photo_id , json_agg(t) AS tags
FROM photo_tag pt
JOIN tag t USING (tag_id)
GROUP BY 1
) t USING (photo_id);
一旦我们检索到足够的行,这比 LATERAL
子查询更便宜。适用于Postgres9.3+。
请注意连接条件中的 USING
子句。这样,我们就可以方便地在外部查询中使用 SELECT *
而不会为 photo_id
获取重复列。我没有在这里使用 SELECT *
,因为您删除的答案表明您希望对于没有标签/评论的情况,使用空JSON数组而不是NULL。
还要从基本表中删除现有的重复项
你不能只使用 json_agg(DISTINCT json_build_object(...))
,因为数据类型 json
没有相等运算符。参见:
有各种更好的方法:
SELECT *
FROM photo p
CROSS JOIN LATERAL (
SELECT json_agg(to_json(c1.comment)) AS comments1
, json_agg(json_build_object('comment', c1.comment)) AS comments2
, json_agg(to_json(c1)) AS comments3
FROM (
SELECT DISTINCT c.comment
FROM comment c
WHERE c.photo_id = p.photo_id
) c1
) c2
CROSS JOIN LATERAL (
SELECT jsonb_agg(DISTINCT t) AS tags
FROM photo_tag pt
JOIN tag t USING (tag_id)
WHERE pt.photo_id = p.photo_id
) t
WHERE p.photo_id = 2;
展示了4种不同的技术,分别是
comments1
、
comments2
、
comments3
(冗余)和
tags
。
db<>fiddle 在这里
旧版:sqlfiddle 适用于 Postgres 9.3;sqlfiddle 适用于 Postgres 9.6
json_agg(DISTINCT tag.name)
对你应该管用,你试过了吗? - PinnyMjson_agg(tag.*)
)。 - Migwelljson_agg(comment.*) AS comments
。 - Migwell