0

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?

3
  • Java/JDBC certainly allows @> to be use in a PreparedStatement. What is the error you get?
    – user1822
    Aug 20, 2020 at 11:05
  • Your second query isn't the same as the first. I think you would need to use jsonb_contains(jsonb_obj, '{"date_el":"2001-01-31"}'). The second condition would translate to jsonb_obj -> 'date_el' @> '"2001-01-31"'
    – user1822
    Aug 20, 2020 at 11:06
  • the error we see is "operator does not exist: jsonb @> text"
    – user172940
    Aug 20, 2020 at 11:33

1 Answer 1

0

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)
3
  • will it use the gin index when using a cast?
    – user172940
    Aug 20, 2020 at 11:38
  • @user172940: yes, because it's proper jsonb value on the right hand side - but the index usage is driven by the expression on the right hand side anyways
    – user1822
    Aug 20, 2020 at 11:38
  • excellent answer, that worked, thank you for your help.
    – user172940
    Aug 20, 2020 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.