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
CodePudding user response:
I would use the containment operator:
'[1, 2, 3]'::jsonb @> ANY (ARRAY['[1]'::jsonb, '[2]'::jsonb])
CodePudding user response:
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);