Create smaller row from alias and preserve column names

Question

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.

Answer 1

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.

Answer 2

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.