Postgres jsonb数组：查询非空交集

Question

假设我在表格t中有一个名为value的JSONB列，这些JSON块内部包含一个名为tags的字符串列表字段。

我想查询任何标记为"foo"或"bar"的JSON块。

因此，假设表数据如下：

value
---------------------
{"tags": ["other"]}
{"tags": ["foo", "quux"]}
{"tags": ["baz", "bar"]}
{"tags": ["bar", "foo"]}
{"tags": []}

我想写一个类似这样的查询：

select value from t where value->'tags' NONEMPTY_INTERSECTION '["foo", "bar"]'

因此，结果将会是：

value
-----------------------
{"tags": ["foo", "quux"]}
{"tags": ["baz", "bar"]}
{"tags": ["bar", "foo"]}

是否有一种实际的查询方式可以实现这一点，并且这种方式可能会非常快？

- Dan Burton

---

可能是使用数组字段查询JSONB的重复问题。 - iLemming

Answer 1

SELECT DISTINCT t.value
FROM t, jsonb_array_elements(t.value->'tags') tags
WHERE tags.value <@ '["foo", "bar"]'::jsonb;

- Patrick

Answer 2

我正在寻找的运算符是?|，它可以这样使用：

select t.value from t where value->'tags' ?| array['foo','bar'];

以下是测试步骤：

danburton=# select * from jsonb_test;
           value
---------------------------
 {"tags": ["foo"]}
 {"tags": ["other"]}
 {"tags": ["foo", "quux"]}
 {"tags": ["baz", "bar"]}
 {"tags": ["bar", "foo"]}
 {"tags": []}
(6 rows)

danburton=# select jsonb_test.value from jsonb_test where value->'tags' ?| array['foo','bar'];
           value
---------------------------
 {"tags": ["foo"]}
 {"tags": ["foo", "quux"]}
 {"tags": ["baz", "bar"]}
 {"tags": ["bar", "foo"]}
(4 rows)

- Dan Burton