How can I combine two tables to get json array in postgresql?

Question

I have two table in postgresql

Tablel A

id | name | b_codes
---|------|---------
1  | abc  | a,b
2  | def  | null

Table B

code | name
-----|------
a    | xx
b    | yy

How can I get these (the point is json arrays):

query A.id=1:

{id: 1, name:'abc', b_codes:[{code: 'a', name: 'xx'}, {code: 'b', name: 'yy'}]}

query A.id=2:

{id: 2, name:'def', b_codes:[]}

or all:

 id | name | codes
 ---|------|----------------------------------------------------
 1  | abc  | [{code: 'a', name: 'xx'}, {code: 'b', name: 'yy'}]
 2  | def  | []

score 3 · Accepted Answer · 2020-04-25 08:50:24Z

3

You first need to normalize the data model to be able to properly join on the list of codes:

select a.id, a.name, x.*
from table_a a
  left join lateral (
    select b.code, b.name
    from unnest(string_to_array(a.b_codes, ',')) as c(code)
       join table_b b on b.code = c.code
  ) as x on true
;

This returns the following result:

id | name | code | name
---+------+------+-----
 1 | abc  | a    | xx  
 1 | abc  | b    | yy  
 2 | def  |      |

The codes can be aggregated directly in the derived table (sub-query):

select a.id, a.name, coalesce(x.codes, '[]') as codes
from table_a a
  left join lateral (
    select jsonb_agg(jsonb_build_object('code', b.code, 'name', b.name)) as codes
    from unnest(string_to_array(a.b_codes, ',')) as c(code)
       join table_b b on b.code = c.code
  ) as x on true
;

The coalesce() is necessary to get an empty array for id = 2, otherwise the column codes from the derived table would be null.

This can now be converted into JSON values:

select jsonb_build_object('id', a.id, 'name', a.name, 'b_codes', coalesce(x.codes, '[]'))
from table_a a
  left join lateral (
    select jsonb_agg(jsonb_build_object('code', b.code, 'name', b.name)) as codes
    from unnest(string_to_array(a.b_codes, ',')) as c(code)
       join table_b b on b.code = c.code
  ) as x on true
;

Online example

edited Apr 25, 2020 at 8:50

answered Apr 25, 2020 at 8:42

user330315

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

1 Comment

Jasmin Over a year ago

Thanks so much for your reply! That's perfect! BUT if each table has 10 thousands of records, how efficient is the query? (I'm newer, so I really what to know.) Thanks again~

Collectives™ on Stack Overflow

How can I combine two tables to get json array in postgresql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related