GROUP BY and array_agg in Postgres

Question

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?

You've done it in a natural, proper way. LEFT JOIN ... ON TRUE looks a bit strange, just CROSS JOIN is more readable. — klin
– klin, Commented Feb 26, 2017 at 18:15
Thanks @klin - I too like the readability of CROSS JOIN better so have adapted my query accordingly. — Jarym
– Jarym, Commented Feb 26, 2017 at 23:47
Quoted identifiers are hard to read and can give you problems. Only use it if there are illegal characters in the identifiers. Basically only ORMs will need it. — Clodoaldo Neto
– Clodoaldo Neto, Commented Feb 27, 2017 at 11:11

Patrick · Accepted Answer · 2017-02-27 00:15:50Z

2

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").

answered Feb 27, 2017 at 0:15

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jarym Over a year ago

Thanks, yes that's how I found my original solution. I didn't realise I can just add it into the FROM clause, that's VERY cool!. Agree on consistent quoting and the ordering ability is damn handy - thank you again.

Collectives™ on Stack Overflow

GROUP BY and array_agg in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related