title: GROUP BY and array_agg in Postgres
body: <p>I have a query as follows:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>and it returns results as:</p>
  
  <pre><code>id  | _rowId | name
  1   |1       |  A
  2   |1       |  B
  3   |2       |  C
  2   |2       |  B
  </code></pre>
  
  <p>I actually want the results to be grouped by the id column so I've produced this SQL after some trial and error:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and this then produces the correct results:</p>
  
  <pre><code>id  | _rowId | name
  1   |{1}     |  A
  2   |{2}     |  C
  3   |{1,2}   |  B
  </code></pre>
  
  <p>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?</p>