Question 90621 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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);

  2. 0

    I would use the containment operator:

    '[1, 2, 3]'::jsonb @> ANY (ARRAY['[1]'::jsonb, '[2]'::jsonb])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search