Как выбрать строки Postgres, где хотя бы один элемент JSON соответствует некоторым критериям?

Question

Вот с чем я работаю:

создать тест таблицы (идентификатор INT, данные JSON);
ВСТАВИТЬ в тестовые значения
  (1, '[{"key": 2}, {"key": 1}]'),
  (2, '[{"key": 3}]'),
  (3, '[{"key": 1}]');


выбрать * из теста;
выберите идентификатор из теста, где 1 == ЛЮБОЙ ( json_array_elements(data) ->> 'key');

То, что я пытаюсь сделать, это выбрать все строки, где любой из объектов JSON в data столбец имеет ключ key со значением 1, Я пытаюсь извлечь строки 1 а также 3, Обратите внимание, я не уверен, если сравнение равенства == прямо перед ANY пункт правильный.

Когда я запускаю вышеописанное, я получаю следующую ошибку: ERROR: set-returning functions are not allowed in WHERE

Answer 1

Если вы можете использовать jsonbвместо json (что предпочтительно в большинстве случаев), используйте jsonb оператор "содержит" @>:

SELECT *
FROM   test
WHERE  data  @> '[{"key": 1"}]';

Может поддерживаться индексом GIN с классом оператора по умолчанию или с более специализированным jsonb_path_ops:

CREATE INDEX test_data_gin_idx ON test USING gin (data jsonb_path_ops);

дБ <> скрипка здесь

Связанные с:

• Индекс для поиска элемента в массиве JSON

Answer 2

Ты можешь использовать EXISTS и коррелированный подзапрос, чтобы выполнить то, что вы хотите.

SELECT test.id
       FROM test
       WHERE EXISTS (SELECT *
                            FROM json_array_elements(test.data) jar(e)
                            WHERE jar.e->>'key' = '1');

SQL Fiddle