jsonb_set - Comprueba si una matriz JSON de Postgres contiene una cadena

jsonb_set example

Question

Tengo una mesa para almacenar información sobre mis conejos. Se parece a esto:

create table rabbits (rabbit_id bigserial primary key, info json not null);
insert into rabbits (info) values
  (''{"name":"Henry", "food":["lettuce","carrots"]}''),
  (''{"name":"Herald","food":["carrots","zucchini"]}''),
  (''{"name":"Helen", "food":["lettuce","cheese"]}'');

¿Cómo debo encontrar a los conejos a los que les gustan las zanahorias? Se me ocurrió esto:

select info->>''name'' from rabbits where exists (
  select 1 from json_array_elements(info->''food'') as food
  where food::text = ''"carrots"''
);

No me gusta esa consulta. Es un desastre.

Como cuidador de conejos a tiempo completo, no tengo tiempo para cambiar el esquema de mi base de datos. Solo quiero alimentar adecuadamente a mis conejos. ¿Hay una forma más legible de hacer esa consulta?

Answer 1

A partir de PostgreSQL 9.4, puede usar el ? operador :

select info->>''name'' from rabbits where (info->''food'')::jsonb ? ''carrots'';

Incluso puedes indexar el ? consulta en la tecla "food" si cambia al tipo jsonb en su lugar:

alter table rabbits alter info type jsonb using info::jsonb;
create index on rabbits using gin ((info->''food''));
select info->>''name'' from rabbits where info->''food'' ? ''carrots'';

Por supuesto, probablemente no tengas tiempo para eso como un cuidador de conejos a tiempo completo.

Actualización: Aquí hay una demostración de las mejoras de rendimiento en una mesa de 1,000,000 conejos donde a cada conejo le gustan dos alimentos y 10% de ellos como las zanahorias:

d=# -- Postgres 9.3 solution
d=# explain analyze select info->>''name'' from rabbits where exists (
d(# select 1 from json_array_elements(info->''food'') as food
d(#   where food::text = ''"carrots"''
d(# );
 Execution time: 3084.927 ms

d=# -- Postgres 9.4+ solution
d=# explain analyze select info->''name'' from rabbits where (info->''food'')::jsonb ? ''carrots'';
 Execution time: 1255.501 ms

d=# alter table rabbits alter info type jsonb using info::jsonb;
d=# explain analyze select info->''name'' from rabbits where info->''food'' ? ''carrots'';
 Execution time: 465.919 ms

d=# create index on rabbits using gin ((info->''food''));
d=# explain analyze select info->''name'' from rabbits where info->''food'' ? ''carrots'';
 Execution time: 256.478 ms

Answer 2

No más inteligente pero más simple:

select info->>''name'' from rabbits WHERE info->>''food'' LIKE ''%"carrots"%'';

Answer 3

Podría usar el operador @> para hacer algo así como

SELECT info->>''name''
FROM rabbits
WHERE info->''food'' @> ''"carrots"'';

Answer 4

Una pequeña variación, pero nada nuevo de hecho. Realmente le falta una característica ...

select info->>''name'' from rabbits 
where ''"carrots"'' = ANY (ARRAY(
    select * from json_array_elements(info->''food''))::text[]);