Postgresql replace an array of ids in jsonb with more columns

Question

Given a jsonb value in the source table like :

SOURCE_TABLE
id | jsonb
---+------
 1 | {"col_1": "A", "col_2": ["A", "B", "C"], "col_3": [1, 2, 3]}
 2 | ...

and a lookup table:

LOOKUP_TABLE
id | val_1 | val_2
---+-------+------
 1 |  aaa  |  AAA
 2 |  bbb  |  BBB
 3 |  ccc  |  CCC

I want to SELECT a new jsonb by replacing "col_3" ids with the details from the lookup table, like:

id | new_jsonb
---+------
 1 | {"col_1": "A", "col_2": ["A", "B", "C"], "col_3": [{"val_1": "aaa", "val_2": "AAA"}, {"val_1": "bbb", "val_2": "BBB"}, {"val_1": "ccc", "val_2": "CCC"}]}
 2 | ...

How would you write the SQL statement?

Pooya · Accepted Answer · 2022-01-26 06:37:09Z

You can use CROSS JOIN and jsonb_array_elements_text functions to extract the JSON array and then join with the second table. After that, you just aggregate and combine JSON result

Demo

select
  st.id,
  st.jsonb || jsonb_build_object('col_3', jsonb_agg(jsonb_build_object('val_1', lt.val_1, 'val_2', lt.val_2)))
from
  SOURCE_TABLE st
  cross join jsonb_array_elements_text(st.jsonb -> 'col_3') st_e
  inner join LOOKUP_TABLE lt on lt.id = st_e.value :: numeric
group by st.id, st.jsonb

Note:

The || operator concatenates two JSON objects by generating an object containing the union of their keys, taking the second object's value when there are duplicate keys. All other cases produce a JSON array: first, any non-array input is converted into a single-element array, and then the two arrays are concatenated. It does not operate recursively; only the top-level array or object structure is merged. (Postgres documents)

user1589188 · Accepted Answer · 2022-01-26 08:18:38Z

Thanks @Pooya, I ended up writing something quite similar to yours using jsonb_set and join lateral:

select
  st.id,
  jsonb_set(st.jsonb, '{col_3}', jsonb_agg(to_jsonb(lt))) new_jsonb
from
  SOURCE_TABLE st, jsonb_array_elements_text(st.jsonb -> 'col_3') st_e
  join lateral (
    select val_1, val_2
    from LOOKUP_TABLE lt
    where lt.id = st_e.value :: numeric
  ) lt on true
group by st.id, st.jsonb

and also a sub select

select
  st.id,
  (
    select jsonb_set(st.jsonb, '{col_3}', jsonb_agg(to_jsonb(lt)))
    from (
      select val_1, val_2
      from jsonb_array_elements_text(st.jsonb -> 'col_3') st_e
      join LOOKUP_TABLE lt on lt.id = st_e.value :: numeric
    ) lt
  ) new_jsonb
from SOURCE_TABLE st

Feel free to comment on my takes

Collectives™ on Stack Overflow

Postgresql replace an array of ids in jsonb with more columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related