Spring data native query does not allow Postgres jsonb string exist operator (question mark)

Question

I am trying to user Postgres jsonb string exist operator in the SpringData native query.

SpringData method example:

@Query(value = "SELECT t.id \n"
        + " FROM task AS t \n"
        + " WHERE (t.worker_ids \\? :workerId)\n"
        + " ORDER BY t.created_at\n",
        nativeQuery = true)
Optional<String> findMatchingTaskId(@Param("workerId") String workerId);

Where worker_idsis of type JSOB in the database. I've tried to exclude question mark with \\ but still got below error : org.postgresql.util.PSQLException: No value specified for parameter 2.

Is there a way to use this operator with spring data native query?

@Cepr0 Why not create an IMMUTABLE function? dba.stackexchange.com/a/91007 — Alex78191
– Alex78191, Commented Jun 19 at 16:14

Yegor Usoltsev · Accepted Answer · 2018-11-29 13:41:11Z

9

All operators in PostgreSQL uses underlying procedure:

> SELECT oprname, oprcode FROM pg_operator WHERE oprname LIKE '%?%'

oprname | oprcode
--------------------------
?       | jsonb_exists
?|      | jsonb_exists_any
?&      | jsonb_exists_all
...

So you can rewrite your query using jsonb_exists(jsonb, text) like this:

SELECT t.id
FROM task AS t
WHERE jsonb_exists(t.worker_ids, :workerId)
ORDER BY t.created_at

answered Nov 29, 2018 at 13:41

Yegor Usoltsev

1461 silver badge5 bronze badges

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

1 Comment

Alex78191 Jun 19 at 16:09

But jsonb_exists doesn't use gin index instead of ? operator. stackoverflow.com/questions/78237556/… dba.stackexchange.com/questions/90002/…

Collectives™ on Stack Overflow

Spring data native query does not allow Postgres jsonb string exist operator (question mark)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related