Spring data native query does not allow Postgres j

问题:

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?

回答1:

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