On Fri, Sep 9, 2016 at 8:30 AM, Tim Uckun <timuckun@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
