Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is there a way to fix this ugliness

Is there a way to fix this ugliness

From
Tim Uckun
Date:
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?

I searched on the internet and somebody suggested coalesce but no matter what combination I tried I could not make it happen.

Thanks.

Re: Is there a way to fix this ugliness

From
Merlin Moncure
Date:
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

Re: Is there a way to fix this ugliness

From
Karl Czajkowski
Date:
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

Re: Is there a way to fix this ugliness

From
Tim Uckun
Date:
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...


Karl