Обсуждение: Is there a way to fix this ugliness

On Fri, Sep 9, 2016 at 8:30 AM, Tim Uckun <timuckun@gmail.com> wrote:
> 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
>
> select
>     ob.id
>      ,case when array_position(array_agg(im.image_type), null) = 1  then
> '[]' else      json_agg(row_to_json(im.*)) end as images
>      from observations ob
>    left join images im on ob.id = im.observation_id
> group by 1
>
>
> The reason I have the case statement there is because some observations
> don't have images but the json_agg(row_to_json function returns [NULL]
> instead of [] which is what I really want.
>
> Is there a more elegant way to do this?

not exactly.  More elegant approaches are frustrated by the lack of a
json operator.  However, you can wrap that in a function.

create or replace function fixnull(j json) returns json as
$$
  select case when j::text = '[null]'::text
      then '[]'::json
      else j
    end;
$$ language sql immutable;

select
    ob.id,
    fixnull(json_agg(to_json(im.*))) as images
     from observations ob
   left join images im on ob.id = im.observation_id
group by 1;

merlin

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