Postgresql replace an array of ids in jsonb with more columns-CodePudding

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?

CodePudding user response：

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)

CodePudding user response：

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