[SOLVED] PostgreSQL jsonb, `?` and JDBC

PostgreSQL jsonb, `?` and JDBC

I am using PostgreSQL 9.4 and the awesome JSONB field type. I am trying to query against a field in a document. The following works in the psql CLI

SELECT id FROM program WHERE document -> 'dept' ? 'CS'

When I try to run the same query via my Scala app, I'm getting the error below. I'm using Play framework and Anorm, so the query looks like this

SQL(s"SELECT id FROM program WHERE document -> 'dept' ? {dept}")
.on('dept -> "CS")
....

SQLException: : No value specified for parameter 5. (SimpleParameterList.java:223)

(in my actual queries there are more parameters)

I can get around this by casting my parameter to type jsonb and using the @> operator to check containment.

SQL(s"SELECT id FROM program WHERE document -> 'dept' @> {dept}::jsonb")
.on('dept -> "CS")
....

I'm not too keen on the work around. I don't know if there are performance penalties for the cast, but it's extra typing, and non-obvious.

Is there anything else I can do?

Solution

As a workaround to avoid the ? operator, you could create a new operator doing exactly the same.

This is the code of the original operator:

CREATE OPERATOR ?(
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

SELECT '{"a":1, "b":2}'::jsonb ? 'b'; -- true

Use a different name, without any conflicts, like #-# and create a new one:

CREATE OPERATOR #-#(
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

SELECT '{"a":1, "b":2}'::jsonb #-# 'b'; -- true

Use this new operator in your code and it should work.

Check pgAdmin -> pg_catalog -> Operators for all the operators that use a ? in the name.