Postgresql – Create smaller row from alias and preserve column names

aggregatepostgresqlrow

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.

Best Answer

One idea is to create an inline view containing the columns you are interested in:

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
CROSS JOIN LATERAL (
    SELECT name, created 
    FROM remodel.sites AS s
    WHERE s.id=user_sites.site
) AS sites
GROUP BY "users".id

By using LATERAL you can reference the "sibling" in the inline view. Since the JOIN condition is part of the view a CROSS JOIN can be used.