Search code examples
postgresqlfilteringjsonb

'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] expression equivalent for integer array

Is there a way to achieve this functionality for integer array?

jsonb ?| text[] → boolean
Do any of the strings in the text array exist as top-level keys or array elements?
'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t

the description above is from postgresql docs, I would like to achieve same thing to filter json array of integers if it contains any from integer array, something like this:

'[1, 2, 3]'::jsonb ?| array[1, 2]

but I'm getting an error

[42883] ERROR: operator does not exist: jsonb ?| integer[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 87
Solution

  • After trying multiple versions I think this one is the simplest I've got:

    SELECT DISTINCT t.*
FROM "Table" t
JOIN jsonb_array_elements_text(t."TypeJson"::jsonb) "Type" ON TRUE
WHERE "Type"::integer in (1, 2, 3);