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@isi.edu> 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...