0
votes

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?