example - ¿Cómo consultar una columna json para objetos vacíos?

postgresql jsonb example

Question

Buscando todas las filas donde una cierta columna json contiene un objeto vacío, {} . Esto es posible con arreglos JSON, o si estoy buscando una clave específica en el objeto. Pero solo quiero saber si el objeto está vacío. Parece que no puede encontrar un operador que haga esto.

 dev=# /d test
     Table "public.test"
  Column | Type | Modifiers
 --------+------+-----------
  foo    | json |

 dev=# select * from test;
    foo
 ---------
  {"a":1}
  {"b":1}
  {}
 (3 rows)

 dev=# select * from test where foo != ''{}'';
 ERROR:  operator does not exist: json <> unknown
 LINE 1: select * from test where foo != ''{}'';
                                      ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
 dev=# select * from test where foo != to_json(''{}''::text);
 ERROR:  operator does not exist: json <> json
 LINE 1: select * from test where foo != to_json(''{}''::text);
                                      ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
 dwv=# select * from test where foo != ''{}''::json;
 ERROR:  operator does not exist: json <> json
 LINE 1: select * from test where foo != ''{}''::json;
                                      ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

A partir de PostgreSQL 9.5 este tipo de consulta con datos JSON no es posible. Por otro lado, acepto que sería muy útil y creé una solicitud para ello:

https://postgresql.uservoice.com/forums/21853-general/suggestions/12305481-check-if-json-is-empty

¡Siéntase libre de votarlo y espero que se implemente!

Answer 2

En 9.3 es posible contar los pares en cada objeto y filtrar los que no tienen ninguno

create table test (foo json);
insert into test (foo) values
(''{"a":1, "c":2}''), (''{"b":1}''), (''{}'');

select *
from test
where (select count(*) from json_each(foo) s) = 0;
 foo 
-----
 {}

o prueba la existencia, probablemente más rápido para objetos grandes

select *
from test
where not exists (select 1 from json_each(foo) s);

Ambas técnicas funcionarán sin problemas independientemente del formateo

Answer 3

No existe un operador de igualdad (o desigualdad) para el tipo de datos json como un todo, porque la igualdad es difícil de establecer. Le encantará jsonb en Postgres 9.4, donde esto es posible. Más detalles en esta respuesta relacionada en dba.SE (último capítulo):

• ¿Cómo eliminar elementos conocidos de una matriz JSON [] en PostgreSQL?

Al enviar ambos lados de la expresión al text permiten operadores = o <> , pero normalmente no es confiable, hay muchas posibles representaciones de texto para el mismo valor json .

Para este caso particular, sin embargo, funciona bien:

select * from test where foo::text <> ''{}''::text;