PostgreSQL jsonb，`和JDBC [英] PostgreSQL jsonb, `?` and JDBC

问题描述

我正在使用PostgreSQL 9.4和超赞的JSONB字段类型.我正在尝试针对文档中的字段进行查询.以下在psql CLI中起作用

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'

当我尝试通过我的Scala应用运行相同的查询时，出现以下错误.我正在使用Play框架和Anorm，因此查询看起来像这样

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::没有为参数5指定值. (SimpleParameterList.java:223)

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

(在我的实际查询中，还有更多参数)

(in my actual queries there are more parameters)

我可以通过将参数强制转换为jsonb并使用@>运算符检查约束来解决此问题.

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?

推荐答案

作为避免此问题的一种解决方法?运算符，您可以创建新运算符进行完全相同的操作.

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.

对于所有使用?的运算符，请检查pgAdmin-> pg_catalog->运算符.的名字.

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

这篇关于PostgreSQL jsonb，`和JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！