CODEWITHSUNDEEP
sqljsonpostgresql
Emmanuel BRUNET
Emmanuel BRUNET

Reputation: 1406

Postgres : how to join tables from json list items

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

Upvotes: 1

Views: 304

Answers (1)

D-Shih
D-Shih

Reputation: 46219

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

sqlfiddle

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

sqlfiddle

Upvotes: 2

Related Questions