postgresql - Postgres 中的 GROUP BY 和 array_agg

Question

我有一个查询如下:

WITH "data" ("displayName","habitas","_rowId") AS (VALUES
 ('Moo','[{"id":"1", "name": "A"},{"id":"2", "name": "B"}]'::json,1)
,('Boo','[{"id":"3", "name": "C"},{"id":"2", "name": "B"}]'::json,2))
SELECT
  t.id, "data"."_rowId", t.name
FROM "data"
    CROSS JOIN
    json_to_recordset("data"."habitas") as t("id" text, "name" text)

返回结果为:

id  | _rowId | name
1   |1       |  A
2   |1       |  B
3   |2       |  C
2   |2       |  B

我实际上希望结果按 id 列分组，所以我在反复试验后生成了这个 SQL:

WITH "data" ("displayName","habitas","_rowId") AS (VALUES
 ('Moo','[{"id":"1", "name": "A"},{"id":"2", "name": "B"}]'::json,1)
,('Boo','[{"id":"3", "name": "C"},{"id":"2", "name": "B"}]'::json,2))
SELECT
  t.id, array_agg("data"."_rowId"), t.name
FROM "data"
    CROSS JOIN
    json_to_recordset("data"."habitas") as t("id" text, "name" text)
GROUP BY t.id, t.name

然后这会产生正确的结果:

id  | _rowId | name
1   |{1}     |  A
2   |{2}     |  C
3   |{1,2}   |  B

它看起来相当不错而且似乎可以工作，但我想知道我是否错过了以更好的方式构建此查询的任何技巧？

Answer 1

一些小问题:

• 由于您在行源 "data" 和函数 json_to_recordset() 之间执行隐式横向连接，您实际上没有在两个行源之间进行不合格的连接但是 "data" 中的一行与函数生成的任何行之间的连接。 CROSS JOIN 子句暗示了一个不合格的连接，它掩盖了这是一个横向连接的事实(所以我更喜欢你的原始版本，如果 LEFT 子句被删除)。在这种情况下，我会简单地使用好的 ol' 逗号 , 列表:FROM "data", json_to_recordset("data"."habitas")。
• 您应该始终如一地在标识符周围使用双引号 "。
• 如果你想保证 "_rowId" 值在数组中是有序的，那么你应该使用 array_agg("data"."_rowId"ORDER BY "data"。 "_rowId").