I want to join two table, with first table contain an array which is primary keys of second table in JSON format
Advertisement
Answer
You can use the ANY
function:
SELECT * FROM a JOIN b ON a.id = ANY(ids)
Edit:
If you have jsonb
arrays, you can use the @>
operator. Note, that this works only if your integer id
values are cast into type jsonb
as well. Since a type int
is not directly castable into type jsonb
, your need the intermediate step via type text
, which yield the strange syntax id::text::jsonb
:
SELECT * FROM a JOIN b ON b.ids @> a.id::text::jsonb
If your column is of type json
instead jsonb
, you need to cast it into type jsonb
because otherwise the operator would not work:
b.ids::jsonb @> ...