Tag Info

New answers tagged

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 ...


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 ...



Top 50 recent answers are included