rails - postgresql jsonb query

PostgreSQL jsonb, `?` Y JDBC

Question

Estoy usando PostgreSQL 9.4 y el impresionante tipo de campo JSONB. Estoy tratando de consultar contra un campo en un documento. Los siguientes trabajos en la CLI psql

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

Cuando trato de ejecutar la misma consulta a través de mi aplicación Scala, obtengo el siguiente error. Estoy usando Play Framework y Anorm, por lo que la consulta se ve así

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

SQLException:: No se especificó ningún valor para el parámetro 5. (SimpleParameterList.java:223)

(en mis consultas reales hay más parámetros)

Puedo jsonb esto lanzando mi parámetro para escribir jsonb y usar el operador @> para verificar la contención.

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

No estoy muy interesado en el trabajo. No sé si hay castigos de rendimiento para el elenco, pero es un tipeo extra, y no obvio.

¿Hay algo mas que pueda hacer?

Answer 1

Como una solución para evitar el? operador, podría crear un nuevo operador haciendo exactamente lo mismo.

Este es el código del operador original:

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

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

Use un nombre diferente, sin ningún tipo de conflicto, como # - # y cree uno nuevo:

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

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

Use este nuevo operador en su código y debería funcionar.

Compruebe pgAdmin -> pg_catalog -> Operadores para todos los operadores que usan un? en el nombre.

Answer 2

En JDBC (y SQL estándar), el signo de interrogación se reserva como marcador de posición de parámetro. Otros usos no están permitidos.

Consulte ¿Prevalece la especificación JDBC ''?'' de ser utilizado como operador (fuera de las comillas)? y la discusión sobre jdbc-spec-discuss .

El controlador JDBC de PostgreSQL actual transformará todas las ocurrencias (texto externo o comentarios) de un signo de interrogación en un marcador de posición de parámetro específico de PostgreSQL. No estoy seguro de si el proyecto PostgreSQL JDBC ha hecho algo (como la introducción de un escape como se discutió en los enlaces anteriores) para abordar esto todavía. Una mirada rápida al código y la documentación sugiere que no lo hicieron, pero no profundicé demasiado.

Anexo : Como se muestra en la respuesta de bobmarksie , las versiones actuales del controlador PostgreSQL JDBC ahora admiten escaparse del signo de interrogación al doblarlo (es decir: ¿usar ?? lugar de ? ).

Answer 3

Tuve el mismo problema hace un par de días y después de algunas investigaciones encontré esto.

https://jdbc.postgresql.org/documentation/head/statement.html

En JDBC, el signo de interrogación ( ? ) Es el marcador de posición para los parámetros posicionales de un Estado Preparado. Sin embargo, hay una serie de operadores PostgreSQL que contienen un signo de interrogación. Para evitar que dichos signos de interrogación en una declaración SQL se interpreten como parámetros posicionales, use dos signos de interrogación ( ?? ) como secuencia de escape. También puede usar esta secuencia de escape en un Statement, pero eso no es obligatorio. Específicamente, solo en un extracto se puede usar un único ( ? ) Como operador.

El uso de 2 signos de interrogación parecía funcionar bien para mí: estaba usando el siguiente controlador (ilustrado usando la dependencia de maven) ...

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.4-1201-jdbc41</version>
    </dependency>

... y MyBatis para crear las consultas SQL y parecía funcionar bien. Parecía más fácil / más limpio que crear un operador PostgreSQL.

SQL pasó de, por ejemplo

select * from user_docs where userTags ?| array[''sport'',''property'']

... a ...

select * from user_docs where userTags ??| array[''sport'',''property'']

¡Espero que esto funcione con tu situación!