6

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.

  • In 9.4 you can use json_object, but you'll still have to repeat all the column names. – Craig Ringer Aug 11 '15 at 1:45
3

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.

  • 1
    This reply solved the problem and performs better in the benchmarks against my database. As I have no intention of using JSON returns this is the most useful answer. – Gauss Aug 12 '15 at 8:54
4

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:


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.