Assuming I've a json column "my_json" in a psql table "A":
['key_A', 'key_B', 'key_C']
and a table B with records
| id | value |
|---|---|
| key_A | value_A |
| key_B | value_B |
| key_C | value_C |
, how can I join those 2 tables ? I tried
select
jsonb_array_elements_text(my_json) as key, BT.value
from A as AT
inner join B as BT
on AT.key = BT.id
but it does not work errror : column AT.key does not exists
You can try to use json_array_elements function.
SELECT *
FROM json_array_elements('["A", "B", "C"]')
If your situation want to get an array from columns of tables you can try this query below
SELECT value
FROM T CROSS JOIN lateral json_array_elements(col) v
EDIT
I saw you modify your question, You can try to use json_array_elements_text get values from your array data from JSON , then do JOIN
SELECT a.*,b.*
FROM A a
CROSS JOIN lateral json_array_elements_text(a.col) v
INNER JOIN B b ON v.value = b.ID
