jsonb existential operators with parameterised queries

Question

...or the question mark question.

I am currently implementing the search functionality for a postgres database, in php, that uses the new jsonb type.

To achieve this I am executing prepared statements with named placeholders.

However I have run into an interesting problem whilst trying to use some of the new postgres JSON containment and existence operators along with named placeholders.

The basis of issue being that the operators themselves use the question mark ? as part of their syntax. i.e.

? Does the key/element string exist within the JSON value?

?| Do any of these key/element strings exist?

?& Do all of these key/element strings exist?

This means I have statements that look like this in PHP.

$sth = $dbh->prepare("SELECT * FROM stuff WHERE meta ? :value");
$sth->bindValue(1, $value, PDO::PARAM_STR);
$sth->execute();

This fails because the question mark is being interpreted as placeholder. To work around this I have tried to make the operator itself a named parameter like so.

$sth = $dbh->prepare("SELECT * FROM stuff WHERE meta :operator :value");
$sth->bindValue(1, $operator, PDO::PARAM_STR);
$sth->bindValue(2, $value, PDO::PARAM_STR);
$sth->execute();

However this just throws the same error as using the bare operator, i.e.

ERROR: syntax error at or near \"$1\"1

Has anyone else come across this issue or can anyone think of a good workaround?

Is there a way to escape or pass the question mark so that one can use the postgres jsonb containment and existence operators with PDO parameterized queries?

Answer 1

you can use corresponding functions instead of operators (jsonb_exists, jsonb_exists_any, jsonb_exists_all). for example run \do+ "?" in psql to see function name of ? operator.

or define your own operator without "?" symbol instead.

For example:

CREATE OPERATOR ~@ (LEFTARG = jsonb, RIGHTARG = text, PROCEDURE = jsonb_exists)    
CREATE OPERATOR ~@| (LEFTARG = jsonb, RIGHTARG = text[], PROCEDURE = jsonb_exists_any)
CREATE OPERATOR ~@& (LEFTARG = jsonb, RIGHTARG = text[], PROCEDURE = jsonb_exists_all)  

So that one can use ~@, ~@| and ~@& in place of ?, ?| and ?& respectively. e.g.

$sth = $dbh->prepare("SELECT * FROM stuff WHERE meta ~@ :value");
$sth->bindValue(1, $value, PDO::PARAM_STR);
$sth->execute();