I want to join two table, with first table contain an array which is primary keys of second table in JSON format
1
-
1Please edit your question (by clicking on the edit link below it) and add some sample data and the expected output based on that data as formatted text. See here for some tips on how to create nice looking text tables. (edit your question - do not put code or additional information in comments)user330315– user3303152021-02-24 07:31:17 +00:00Commented Feb 24, 2021 at 7:31
Add a comment
|
1 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 @> ...
