Postgres JSONB Check if JSON contains string using JPA

Question

I have a jsonb column in my posgtgres table the json is of structure : {'alias':["name1","name2","name"....]}

I have written the Postgres query to check if the array in the JSON object contains the name.

select * from public.table t where json_col->'alias' ? 'name'

this works on pgAdmin

But same doesn't work in JPA

My code

@Query(value = "select * from public.table t where json_col->'alias' ? :name" ,nativeQuery = true)
    Table findUsingName(@Param("name") String name);

This throws an error : 'Mixing of ? parameters and other forms like ?1 is not supported!'

I understand this error is due to the fact ? is also used by JPA is different sense...

Can anyone help me in the JPA Query

Try ?? instead. I think the JDBC driver will detect that's an "escaped" ? operator. — user330315
– user330315, Commented Jan 14, 2021 at 7:40

Christian Beikov · Accepted Answer · 2021-01-14 07:37:56Z

1

I don't know what the ? operator does, but you should use the named variant of the operator as a ? in JDBC is used to define a parameter.

answered Jan 14, 2021 at 7:37

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Utkarsh Sharma · Accepted Answer · 2021-01-16 19:42:05Z

0

Found the postgres function for '?' i.e. jsonb_exists_any(company_alias->'alias',ARRAY[:name])

answered Jan 16, 2021 at 19:42

Utkarsh Sharma

516 bronze badges

2 Comments

user330315 Over a year ago

The disadvantage of using the function is that it can't make use of an index if you need that for performance reasons.

Utkarsh Sharma Over a year ago

I tried ?? but this the error: 'Mixing of? parameters and other forms like ? 1 is not supported!' I had no other option

Collectives™ on Stack Overflow

Postgres JSONB Check if JSON contains string using JPA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related