Как запросить json-столбец для пустых объектов?

Ищите все строки, где определенный столбец json содержит пустой объект, {}. Это возможно с массивами JSON, или если я ищу конкретный ключ в объекте. Но я просто хочу знать, пустой ли объект. Кажется, не может найти оператора, который сделает это.

 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.

Ответ 1

Не существует оператора равенства (или неравенства) для типа данных json в целом, потому что равенство трудно установить. Рассмотрим jsonb в Postgres 9.4 или новее, где это возможно. Подробнее в этом ответе на dba.SE (последняя глава):

Как удалить известные элементы из массива JSON [] в PostgreSQL?

SELECT DISTINCT json_column... или ... GROUP BY json_column ошибкой по той же причине (без оператора равенства).

Приведение обеих сторон выражения к text допускает операторы = или <>, но это обычно ненадежно, поскольку существует много возможных текстовых представлений для одного и того же значения json.

Тем не менее, для этого конкретного случая (пустой объект) это работает просто отлично:

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

Ответ 2

По состоянию на PostgreSQL 9.5 этот тип запроса с данными JSON невозможен. С другой стороны, я согласен, что это было бы очень полезно и создало бы запрос на него:

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

Не стесняйтесь голосовать, и, надеюсь, он будет реализован!

Ответ 3

В 9.3 можно считать пары в каждом объекте и фильтровать те, у которых нет

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 
-----
 {}

или проверить существование, возможно, быстрее для больших объектов

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

Оба метода будут работать безупречно, независимо от их формирования