Re: Is there a way to fix this ugliness - Mailing list 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