如何使用IN运算符查询jsonb数组

Question

我正在寻找一种使用数组中的“ IN”子句查询postgres jsonb字段的方法。

假设我有一张桌子

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数组中的label可能为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)

表现

该jsonb_array_elements()功能很昂贵。对于较大的表，由于服务器负载沉重且查询执行时间长，该功能的使用可能会令人怀疑。

虽然GIN索引可用于与@>运算符的查询：

CREATE INDEX ON test USING GIN (test_content)

对于该功能，这是不可能的。索引支持的查询可能比使用该函数的查询快几十倍。