Postgres jsonb数组:查询非空交集 [英] Postgres jsonb array: query for non-empty intersection

问题描述

假设我在表t中有一个名为value的JSONB列，并且在这些blob的内部是一个tags字段，该字段是字符串列表.

Suppose I have a JSONB column called value in a table t, and inside of these blobs of JSON is a tags field which is a list of strings.

我想查询标记为"foo"或"bar"的任何这些JSON Blob.

I'd like to make a query for any of these JSON blobs tagged "foo" or "bar".

因此，假设表数据如下所示:

So suppose the table data looks like this:

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"]}

是否有一个实际的查询可以完成此任务，并且有什么方法可以使其快速进行吗?

Is there an actual query that will accomplish this, and is there any way that it could possibly be fast?

推荐答案

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

The operator I was looking for is ?|, which can be used like so:

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)

这篇关于Postgres jsonb数组:查询非空交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！