带有参数化查询的jsonb存在运算符 [英] jsonb existential operators with parameterised queries

问题描述

...或问号问题.

我目前正在使用新的jsonb类型在php中实现Postgres数据库的搜索功能.

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.

但是，在尝试使用一些新的postgres时遇到了一个有趣的问题

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.

?键/元素字符串是否存在于JSON值中?

? 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?

这意味着我在PHP中有类似以下的语句.

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?

是否有一种转义或传递问号的方法，以便可以将Postgres jsonb包含和存在运算符与PDO参数化查询一起使用?

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?

推荐答案

，您可以使用相应的函数代替运算符(jsonb_exists，jsonb_exists_any，jsonb_exists_all).例如在psql中运行\do+ "?"以查看函数名称?运算符.

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.

例如:

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();

这篇关于带有参数化查询的jsonb存在运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！