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?