CODEWITHSUNDEEP
sqlpostgresql
prajay
prajay

Reputation: 3

Join table with mapping of array in postgres

I want to join two table, with first table contain an array which is primary keys of second table in JSON format

Upvotes: 0

Views: 564

Answers (1)

S-Man
S-Man

Reputation: 23756

demo:db<>fiddle

You can use the ANY function:

SELECT
    *
FROM a
JOIN b ON a.id = ANY(ids)

Edit:

demo:db<>fiddle

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 @> ...

Upvotes: 1

Related Questions