I could not make coalesce in the top level select to work even though people on the internet say it should work. I'll do the sub select that seems like it would work fine.
On Sat, Sep 10, 2016 at 3:22 AM, Karl Czajkowski <karlcz@xxxxxxx> wrote:
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
