在jsonb的原生SQL查询中，问号的用法是什么？

Question

我需要对Postgres的jsonb列执行选择查询：

entityManager.createNativeQuery(
 "select * from table where jsonbcol -> 'usernames' ? :un"
).setParameter("un", userName).getResultList()

运行时抛出异常：

org.hibernate.engine.query.ParameterRecognitionException: Mixed parameter strategies - 
use just one of named, positional or JPA-ordinal strategy

我尝试像这样转义\\? 和 ??，但没用。

如何正确调用它？

- injecteer

---

我会尝试使用 \\?? 和 \\?\\?，但我没有设置它的环境。你可以尝试一下并告诉我们哪个有效，这样会更快。 - coladict

jsonbcol -> 'usernames' \\?\\? :un 运行良好，如果这种措辞可以应用于这种糟糕的代码。 - injecteer

请将@coladict的回答放在答案中，这样我就可以接受它了。 - injecteer

Answer 1

适当的转义序列使查询变成了这样：

entityManager.createNativeQuery(
 "select * from table where jsonbcol -> 'usernames' \\?\\? :un"
).setParameter("un", userName).getResultList()

反斜杠用于转义Hibernate参数检测，两个问号是JDBC转义符。

- coladict

---

三重转义！这对我来说是一个记录 :D 一个用于JDBC，一个用于Hibernate，最后一个用于Java。 - Michele

这是一个很好的答案，解决了我面临的问题。在jsonb中应该转义哪些问号有一些微妙之处。例如，使用json路径表达式的查询，如SELECT * FROM SchemaType s WHERE s.schemadata @? '$.** ? (@.type == "string")'，不应该转义json路径文字'$ .** ? (@.type == "string")'中的问号，而只需要在@?运算符处转义。 - James W

@JamesW 你是怎么逃避 @? 的？我试过 @\\?\\? 但是会报错。 - undefined

Answer 2

有一种可行的替代方案，可以成为一个优雅的解决方法。Postgres操作符 ? 基于 jsonb_exists() 函数，因此可以使用它来代替。

where jsonbcol -> 'usernames' \\?\\? :un

你可以使用

where jsonb_exists(jsonbcol -> 'usernames', :un)

- klin

---

请参见此查询的结果：SELECT * FROM pg_operator WHERE oprname = '?'; - Frank Heikens

你可以使用这些函数，但是如果你创建了任何索引，它们并不会从中受益。 - coladict

它对我使用“jsonb_exists”函数有效，但对于“\?\?”转义字符无效。 - ManojP

Answer 3

如文档所述：

在JDBC中，问号（?）是PreparedStatement的位置参数的占位符。然而，有许多PostgreSQL运算符包含一个问号。为了避免将这些问号解释为位置参数，可以使用两个问号（??）作为转义序列来保留SQL语句中的这些问号。

- Laurenz Albe