From: | Karl Czajkowski <karlcz(at)isi(dot)edu> |
---|---|
To: | Tim Uckun <timuckun(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Is there a way to fix this ugliness |
Date: | 2016-09-09 15:22:52 |
Message-ID: | 20160909152252.GA28678@moraine.isi.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sep 10, Tim Uckun modulated:
> I am trying to get the child elements of a one to many table to be
> rolled up into a json field in the parent table. The query I am running
> is...
The problem is aggregating over the results of the left-outer join,
which introduces NULLs. You can try pushing that down into a sub-query
to create one image row per observation prior to joining:
SELECT
ob.id,
im.images
FROM observations ob
LEFT OUTER JOIN (
SELECT
observation_id,
json_agg(row_to_json(im.*)) AS images
FROM images im
GROUP BY observation_id
) im ON (ob.id = im.observation_id) ;
you might use COALESCE in the top-level SELECT if you want to replace
any NULL im.images with a different empty value constant...
Karl
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Uckun | 2016-09-09 22:36:11 | Re: Is there a way to fix this ugliness |
Previous Message | Alexander Farber | 2016-09-09 14:52:45 | Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN |