Как запросить массивы jsonb с оператором IN

Question

Я ищу способ сделать запрос к полю postgres jsonb с видом предложения "IN" внутри массива.

Давайте предположим, что у меня есть стол

CREATE TABLE test(
   id uuid,
   test_content jsonb,
   PRIMARY KEY(id)
);

INSERT INTO test (id, test_content) VALUES 
('aa82a8b8-33ef-4937-bd8c-8a4b40960f18', '[{"label":"a","label1":"1"},{"label":"b","label1":"2"}]'),
('ba82a8b8-33ef-4937-bd8c-8a4b40960f18', '[{"label":"c","label1":"3"}]'),
('da82a8b8-33ef-4937-bd8c-8a4b40960f18', '[{"label":"d","label1":"4"}]');

Мне нужно выбрать строки, где метка внутри массива test_content может быть b или же d,

Я старался

SELECT * 
FROM test 
WHERE test_content @> '[{"label":"b"}]' OR test_content @> '[{"label":"d"}]'

но когда я хочу расширить свой запрос с label1 содержащий 2 или же 3 это становится сложным...

Что мне нужно, это вид WHERE label IN ('b','d') AND label1 IN ('2','3')

Возможно ли это с операторами JSONB?

Answer 1

Короткий ответ

Вы можете использовать функцию jsonb_array_elements() в боковом соединении и использовать его результат value в сложных выражениях в WHERE пункт:

SELECT t.* 
FROM test t
CROSS JOIN jsonb_array_elements(test_content)
WHERE value->>'label' IN ('b', 'd')
AND value->>'label1' IN ('2', '3')

отчетливый

Запрос может возвращать дублированные строки, когда условия фильтра выполняются в более чем одном элементе массива в одной строке, например

SELECT t.* 
FROM test t
CROSS JOIN jsonb_array_elements(test_content)
WHERE value->>'label' IN ('a', 'b')

                  id                  |                          test_content                          
--------------------------------------+----------------------------------------------------------------
 aa82a8b8-33ef-4937-bd8c-8a4b40960f18 | [{"label": "a", "label1": "1"}, {"label": "b", "label1": "2"}]
 aa82a8b8-33ef-4937-bd8c-8a4b40960f18 | [{"label": "a", "label1": "1"}, {"label": "b", "label1": "2"}]
(2 rows)    

Следовательно, может быть разумно использовать DISTINCT в SELECT список:

SELECT DISTINCT t.* 
FROM test t
CROSS JOIN jsonb_array_elements(test_content)
WHERE value->>'label' IN ('a', 'b')

или же EXISTS в WHERE предложение, которое может быть немного быстрее:

SELECT t.*
FROM test t
WHERE EXISTS (
    SELECT 
    FROM jsonb_array_elements(test_content)
    WHERE value->>'label' IN ('a', 'b')
    )

Вы также можете выбрать соответствующие элементы массива в тех случаях, когда необходима эта информация:

SELECT id, value
FROM test t
CROSS JOIN jsonb_array_elements(test_content)
WHERE value->>'label' IN ('a', 'b')

                  id                  |             value             
--------------------------------------+-------------------------------
 aa82a8b8-33ef-4937-bd8c-8a4b40960f18 | {"label": "a", "label1": "1"}
 aa82a8b8-33ef-4937-bd8c-8a4b40960f18 | {"label": "b", "label1": "2"}
(2 rows)

Perfomance

jsonb_array_elements() функция стоит дорого. Для больших таблиц использование функции может быть сомнительным из-за большой нагрузки на сервер и длительного времени выполнения запроса.

В то время как индекс GIN может использоваться для запросов с @> оператор:

CREATE INDEX ON test USING GIN (test_content)

в случае функции это невозможно. Запросы, поддерживаемые индексом, могут быть в несколько десятков раз быстрее, чем те, которые используют функцию.