PostgreSQL: исключить полный массив jsonb, если один элемент не соответствует предложению WHERE

Предположим, есть таблица json_table со столбцами id (int), data (jsonb). Пример значения jsonb будет

{"a": [{"b":{"c": "xxx", "d": 1}},{"b":{"c": "xxx", "d": 2}}]}

Когда я использую оператор SQL, подобный следующему:

SELECT data FROM json_table j, jsonb_array_elements(j.data#>'{a}') dt WHERE (dt#>>'{b,d}')::integer NOT IN (2,4,6,9) GROUP BY id;

... два элемента массива не являются вложенными, и тот, который соответствует предложению WHERE, все еще возвращается. Это имеет смысл, поскольку каждый элемент массива рассматривается отдельно. В этом примере я верну полную строку

{"a": [{"b":{"c": "xxx", "d": 1}},{"b":{"c": "xxx", "d": 2}}]}

Я ищу способ исключить полную строку json_table, когда какой-либо элемент массива jsonb не соответствует условию


postgresql json sql jsonb postgresql-10
person Georgios Varisteas    schedule 23.02.2020    source источник
comment
На самом деле это PostgreSQL 10.12. Моя ошибка с тегами   -  person Georgios Varisteas    schedule 24.02.2020

Ответы (2)


arrow_upward
0
arrow_downward

Вы можете переместить условие в предложение WHERE и использовать NOT EXISTS:

SELECT data
FROM json_table j
WHERE NOT EXISTS (SELECT 1
                  FROM jsonb_array_elements(j.data#>'{a}') dt 
                  WHERE (dt#>>'{b,d}')::integer IN (2, 4, 6, 9)
                 );
person Gordon Linoff    schedule 23.02.2020

arrow_upward
0
arrow_downward

Вы можете добиться этого с помощью следующего запроса:

select data 
from json_table
where jsonb_path_match(data, '!exists($.a[*].b.d ? ( @ == 2 || @ == 4 || @ == 6 || @ == 9))')
person Catalin M.    schedule 24.02.2020
comment
Я не могу сделать эту работу. Что бы я ни пытался, я получаю следующее ERROR: function jsonb_path_match(jsonb, unknown) does not exist - person Georgios Varisteas; 24.02.2020
comment
На самом деле я использую PostgreSQL 10.12. - person Georgios Varisteas; 24.02.2020
comment
jsonb_path_match() требуется Postgres 12 - person a_horse_with_no_name; 24.02.2020