Check jsonb array element existence in spring data jpa native query

Question

I have to check if there is id=123 among ids in jsonb column with name rules.

Fiddle:

create table test(rules)as values('{"ids": ["123", "456"]}'::jsonb)
                                ,('{"ids": ["123", "777"]}')
                                ,('{"ids": ["999", "456"]}')
                                ,('{"ids": ["999", "777"]}');

The resulting query will be used in the native query spring data jpa 2.7.1, where I cannot use (rules -> 'ids' ? ?1) construction. Otherwise I get

java.lang.IllegalArgumentException: Mixing of ? parameters and other forms like ?1 is not supported!

How can I perform the required check taking this factor into account? I tried using jsonb_exists_any but it didn't help so far.

Answer 1

Using the function disables index support but there is a simple workaround: define an inlineable function wrapper around the operator.

Use that and Postgres will rewrite your query using the function body with the operator call in it, and that will allow it to use the index just as if you used the operator in the first place:

create function _jsonb_exists_(jsonb,text)returns boolean return $1 ? $2;
create function _jsonb_exists_any_(jsonb,text[])returns boolean return $1 ?| $2;

If you're only looking for rows with one specific id in the "ids" array, use the wrapper around the jsonb_exists ? operator:
_{demo at db<>fiddle}

select * from test where _jsonb_exists_(rules->'ids', '123');

rules
{"ids": ["123", "456"]}
{"ids": ["123", "777"]}

EXPLAIN can show you that the index was really used and that your query was understood to use ? - there's no mention of your _jsonb_exists_ wrapper:

explain analyze verbose select*from test where _jsonb_exists_(rules->'ids','123');

QUERY PLAN
Bitmap Heap Scan on public.test (cost=16.08..53.87 rows=10 width=100) (actual time=0.028..0.101 rows=98 loops=1)
Output: rules
Recheck Cond: ((test.rules -> 'ids'::text) ? '123'::text)
Heap Blocks: exact=93
-> Bitmap Index Scan on test_expr_idx (cost=0.00..16.07 rows=10 width=0) (actual time=0.015..0.015 rows=98 loops=1)
Index Cond: ((test.rules -> 'ids'::text) ? '123'::text)
Planning Time: 0.082 ms
Execution Time: 0.120 ms

System catalogs let you look up the function names behind operators in system catalogs, including what operand types they need. You can wrap them all if you want:

select oid::regoperator
     , oprcode
from pg_operator 
where oprleft::regtype::text='jsonb'
  and oprcode::text ilike '%exist%';

oid	oprcode
?(jsonb,text)	jsonb_exists
?|(jsonb,text[])	jsonb_exists_any
?&(jsonb,text[])	jsonb_exists_all
@?(jsonb,jsonpath)	jsonb_path_exists_opr

Note that you only need ?| or jsonb_exists_any() you mentioned when you're using multiple ids and looking for rows where that array overlaps yours. This function doesn't accept a text but a text[] array. If you want to use it for a single id, it still needs to come wrapped in an array.

select * from test where _jsonb_exists_any_(rules->'ids', array['999','456']);

rules
{"ids": ["123", "456"]}
{"ids": ["999", "456"]}
{"ids": ["999", "777"]}