Merge nested ARRAY of JSONB fields with a JOIN-like statement in PostgreSQL?

Question

Given this tables:

BOOKS

 id | data
----+-------------------------------------------------------
 1  | { title: 'Book 1', price: 10.5, authors: [{ id: 1}, { id: 2 }]}
 2  | { title: 'Book 2', price: 11.5, authors: [{ id: 2 } }

AUTHORS

 id | data
-----+-------------------------------------------------------
 1  | { name: 'Author 1', address: 'Address author 1' }
 2  | { name: 'Author 2', address: 'Address author 2' }

Is it possible to obtain this result, by merging the authors key array elements, with a JOIN-like statement or using jsonb functions?

BOOKS QUERY RESULT

 id | data
----+------------------------------------------------------------------------------------------------------
 1  | { title: 'Book 1', price: 10.5, authors: [{ id: 1, name: 'Author 1', address: 'Address author 1' }, { id: 2, name: 'Author 2', address: 'Address author 2'}] }
 2  | { title: 'Book 2', price: 11.5, authors: [{ id: 2, name: 'Author 2', address: 'Address author 2'}] }

Thanks

klin · Accepted Answer · 2018-02-26 17:29:56Z

1

The id arrays have to be unnested and results must be aggregated again what makes the query more complicated. I have changed a bit the arrays structure to more logical.

select b.data || jsonb_build_object('authors', jsonb_agg(a.data || jsonb_build_object('id', a.id)))
from books b
cross join jsonb_array_elements_text(b.data->'authors')
join authors a on a.id = value::int
group by b.id;

SqlFiddle

answered Feb 26, 2018 at 17:29

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cassvail Over a year ago

Thanks again!! From your answer i managed to get the right answer for my specific case, in which I don't have control over nested "useless" id keys

DTown · Accepted Answer · 2018-02-26 18:11:31Z

There may be a more elegant way to do this but:

select
    book_id,
    jsonb_set(max(bdata :: text)::jsonb,
              '{authors}',
              jsonb_agg(nested.adata)) as author_data --aggregate authors into one jsonb arr
from (select
          b.id as book_id,
          b.data as bdata,
          a.data as adata
      from books b
      join authors a
          on b.data->'authors' @> concat('[{"id": ', a.id :: varchar, '}]') :: jsonb) as nested
group by book_id;

Relevant docs: see jsonb_set in https://www.postgresql.org/docs/9.5/static/functions-json.html and jsonb_agg at https://www.postgresql.org/docs/9.5/static/functions-aggregate.html

cassvail · Accepted Answer · 2018-02-27 14:54:13Z

0

select b.data || jsonb_build_object('authors', jsonb_agg(a.data || 
jsonb_build_object('id', a.id)))
from books b
left join (select id, jsonb_array_elements(data->'authors') from books) ba on 
ba.id = b.id
left join authors a on a.id = ba.id
group by b.id;

answered Feb 27, 2018 at 14:54

cassvail

534 bronze badges

Collectives™ on Stack Overflow

Merge nested ARRAY of JSONB fields with a JOIN-like statement in PostgreSQL?

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related