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.