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

Question

Нужно найти все строки, где определенный столбец 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.

Answer 1

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

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

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

Для этого конкретного случая, однако, это работает просто отлично:

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

Answer 2

Пустой массив JSON [] также может быть актуальным.

Тогда это может работать как для [] а также {}:

select * from test where length(foo::text) > 2 ;

Answer 3

Ты должен быть осторожен. Приведение всех ваших данных к другому типу для сравнения приведет к проблемам с производительностью в большой базе данных.

Если у ваших данных есть согласованный ключ, вы можете проверить его наличие. Например, если данные плана: {} или {id: '1'}

тогда вы можете искать предметы без идентификатора

SELECT * FROM public."user"
where NOT(plan ? 'id')

Answer 4

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

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

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

Answer 5

В 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);

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

Answer 6

Согласно документации по функциям и операторам JSON, вы можете использовать функцию двойной стрелки ( ->>), чтобы получить объект json или поле массива в виде текста. Затем выполните проверку равенства для строки.

Так что это сработало для меня:

      SELECT jsonb_col from my_table
WHERE jsonb_col ->> 'key' = '{}';

Или, если он вложен более чем в один уровень, используйте функцию пути ( #>>)

      SELECT jsonb_col from my_table
WHERE jsonb_col #>> '{key, nestedKey}' = '{}';

В настоящее время поддерживаемая версия на момент написания:

Поддерживаемые версии: текущая (13) / 12/11/10 / 9,6

Answer 7

Вы можете использовать Postgres<@(включен в) оператор, см. документацию:

      select '{"a":2, "b":"hello"}'::jsonb <@ '{}'::jsonb;  -- false
select '{}'::jsonb <@ '{}'::jsonb;                    -- true

select '[{"a":1}, {"a":2}, {"a":3}]'::jsonb <@ '[]'::jsonb;  -- false
select '[]'::jsonb <@ '[]'::jsonb;                           -- true

Answer 8

Функции JSON в функциях PostgreSQL 12jsonb_path_exists.

Чтобы избежать сериализации больших объектов jsonb, это правильно возвращает true, если объект не пуст:

      select data from block where jsonb_path_exists(data, '$ ? (exists (@.*))');

Answer 9

      SELECT * FROM Table_Name WHERE JSON_LENGTH(column_name) = 0