with - postgresql jsonb example

Matriz de consultas Postgresql de objetos en el campo JSONB

Question

Tengo una tabla en una base de datos postgresql 9.4 con un campo jsonb llamado receptores. Algunas filas de ejemplo:

[{"id": "145119603", "name": "145119603", "type": 2}]
[{"id": "1884595530", "name": "1884595530", "type": 1}]
[{"id": "363058213", "name": "363058213", "type": 1}]
[{"id": "1427965764", "name": "1427965764", "type": 1}]
[{"id": "193623800", "name": "193623800", "type": 0}, {"id": "419955814", "name": "419955814", "type": 0}]
[{"id": "624635532", "name": "624635532", "type": 0}, {"id": "1884595530", "name": "1884595530", "type": 1}]
[{"id": "791712670", "name": "791712670", "type": 0}]
[{"id": "895207852", "name": "895207852", "type": 0}]
[{"id": "144695994", "name": "144695994", "type": 0}, {"id": "384217055", "name": "384217055", "type": 0}]
[{"id": "1079725696", "name": "1079725696", "type": 0}]

Tengo una lista de valores para id y quiero seleccionar cualquier fila que contenga un objeto con cualquiera de los valores de esa lista, dentro de la matriz en el campo jsonb.

¿Es eso posible? ¿Hay un índice de GIN que pueda hacer que acelere esto?

Answer 1

Encuentro una solución:
where data::text similar to ''%("id": "145119603"|"id": "1884595530")%''

Answer 2

No hay una sola operación que pueda ayudarlo, pero tiene algunas opciones:

1. Si tiene un número pequeño (y fijo) de ID para consultar, puede usar múltiples operadores de contención @> combinados con or ; f.ex .:

where data @> ''[{"id": "1884595530"}]'' or data @> ''[{"id": "791712670"}]''

Un simple índice de gin puede ayudarte en tu columna de datos aquí.

2. Si tiene un número variable de identificadores (o tiene muchos), puede usar json[b]_array_elements() para extraer cada elemento de la matriz, crear una lista de identificadores y luego consultarla con cualquiera. operador de contención ?| :

select *
from   jsonbtest
where  to_json(array(select jsonb_array_elements(data) ->> ''id''))::jsonb ?|
         array[''1884595530'', ''791712670''];

Desafortunadamente, no puede indexar una expresión, que tiene una subconsulta en ella. Si desea indexarlo, necesita crear una función para él:

create function idlist_jsonb(jsonbtest)
  returns jsonb
  language sql
  strict
  immutable
as $func$
  select to_json(array(select jsonb_array_elements($1.data) ->> ''id''))::jsonb
$func$;

create index on jsonbtest using gin (idlist_jsonb(jsonbtest));

Después de esto, puedes consultar las identificaciones de esta manera:

select *, jsonbtest.idlist_jsonb
from   jsonbtest
where  jsonbtest.idlist_jsonb ?| array[''193623800'', ''895207852''];

Nota : utilicé la notación de puntos / campo computado aquí, pero no tienes que hacerlo.

3. Pero en este punto, no tiene que quedarse con json [b]: tiene una matriz de texto simple, que también es compatible con PostgreSQL.

create function idlist_array(jsonbtest)
  returns text[]
  language sql
  strict
  immutable
as $func$
  select array(select jsonb_array_elements($1.data) ->> ''id'')
$func$;

create index on jsonbtest using gin (idlist_array(jsonbtest));

Y consulte este campo calculado con el operador de matriz de superposición && :

select *, jsonbtest.idlist_array
from   jsonbtest
where  jsonbtest.idlist_array && array[''193623800'', ''895207852''];

Nota : a partir de mis pruebas internas, esta última solución se calcula con un costo mayor que la variante jsonb, pero en realidad es un poco más rápido que eso. Si el rendimiento realmente te importa, debes probar ambos.