Create smaller row from alias and preserve column names

Using Postgres:

SELECT users."name" AS "name"
    , array_to_json(array_agg(sites)) as sites
FROM remodel.users AS users
JOIN remodel.user_sites AS user_sites
    ON users.id=user_sites.user
JOIN remodel.sites AS sites
    ON sites.id=user_sites.site
GROUP BY "users".id
;

currently produces

"Toby";"[{"id":1,"name":"Village","created":"2015-08-10T15:22:36.622298"},
{"id":2,"name":"Manor","created":"2015-08-10T15:22:43.614551"}]"
"Amy";"[{"id":3,"name":"Park","created":"2015-08-10T15:22:48.810872"}]"
"Anne";"[{"id":2,"name":"Manor","created":"2015-08-10T15:22:43.614551"},
{"id":1,"name":"Village","created":"2015-08-10T15:22:36.622298"},
{"id":3,"name":"Park","created":"2015-08-10T15:22:48.810872"}]"


however I would like not to have the "id" field in the JSON output.

changing the sites selection to

array_to_json(array_agg(row(sites."name", sites.created))) as sites

causes the fields to lose their names

"[{"f1":"Village","f2":"2015-08-10T15:22:36.622298"},
{"f1":"Manor","f2":"2015-08-10T15:22:43.614551"}]"


and attempting (what would be a horrible) sub selection

, array_to_json(array_agg((SELECT "name", created FROM sites))) as sites

understandably throws an


ERROR: relation "sites" does not exist

And removing the id with a select in the from clause prevents joining the tables.

I'm looking to preserve the column information as well as I can, not just for conversion to JSON but for inclusion in much larger and complex queries, so performing the array selection without using JSON is preferred.

Unfortunately, a ROW() expression does not preserve column names. Use a subselect instead.

And json_agg() is simpler and faster for the task:

SELECT u.id, u.name
     , json_agg((SELECT x FROM (SELECT s.name, s.created) x)) AS sites
FROM   remodel.users      u
JOIN   remodel.user_sites us ON us.user = u.id
JOIN   remodel.sites      s  ON s.site = us.id
GROUP  BY u.id;  -- id must be the PK

The question has been answered before - with detailed explanation and links:

• Select columns inside json_agg

To preserve column names in a plain array (not JSON or hstore), you need to use a registered row type for the row, else column names are lost in any case.

Either you already have a matching row type, or you register one (temporarily):

CREATE TEMP TABLE tmp_site(name text, created timestamptz)  -- use your data types!

Then:

SELECT u.id, u.name
     , array_agg((s.name, s.created)::tmp_site) AS sites  -- or whatever you do with s
FROM   remodel.users      u
JOIN   remodel.user_sites us ON us.user = u.id
JOIN   remodel.sites      s  ON us.site = s.id
GROUP  BY u.id

Details in the linked answer.

StackExchange Database Administrators Q#110613, answer score: 5