postgresql prepared statements with JSONB containment

Question

I'm trying to write a prepared statement using a date held in JSONB. A prepared statement doesn't allow the containment operator @> so I used the function name itself jsonb_contains which executes, but now postgres doesn't use the gin index.

Testing in a psql session performance is excellent when I do this

select * 
from tab 
where jsonb_obj @> '{"date_el":"2001-01-31"}'

because it uses the gin index. But that won't parse in a prepared statement in java.

When I run this in psql:

select * 
from tab 
where jsonb_contains(jsonb_obj->>'date_el', '2001-01-31')

performance is very bad.

Has anyone ever had this problem?

Answer 1

the error we see is "operator does not exist: jsonb @> text"

I assume you are passing the JSON string via PreparedStatement.setString().

Postgres expects a JSONB value, not a text value. You can easily overcome this by casting the parameter to a jsonb:

select * 
from tab 
where jsonb_obj @> cast(? as jsonb)