CODEWITHSUNDEEP
postgresql
Jarym
Jarym

Reputation: 2350

GROUP BY and array_agg in Postgres

I have a query as follows:

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)

and it returns results as:

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

I actually want the results to be grouped by the id column so I've produced this SQL after some trial and error:

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

and this then produces the correct results:

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

It's fairly ok looking and seems to work but I'm wondering if I've missed any tricks to construct this query in a better way?

Upvotes: 2

Views: 3314

Answers (1)

Patrick
Patrick

Reputation: 32161

A few minor points:

  • Since you are performing an implicit lateral join between the row source "data" and the function json_to_recordset() you are effectively not making an unqualified join between two row sources but a join between a row from "data" and whichever rows the function produces. The CROSS JOIN clause implies an unqualified join and it obscures the fact that this is a lateral join (so I liked your original version better, if the LEFT clause is removed). In a case like this I would simply use the good ol' comma , list: FROM "data", json_to_recordset("data"."habitas").
  • You should use double quotes " around identifiers consistently.
  • If you want to guarantee that the "_rowId" values are in order in the array, then you should use array_agg("data"."_rowId" ORDER BY "data"."_rowId").

Upvotes: 2

Related Questions