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:
By using
LATERAL
you can reference the "sibling" in the inline view. Since theJOIN
condition is part of the view aCROSS JOIN
can be used.