PostgreSQL JSONB在多个JSONB列上重叠操作符

Question

我有一个包含两个jsonb列的表，这两个列都由代表字符串数组的jsonb数据组成。这些也可以是空数组。我现在试图查询这个表并检索行，其中一个(或两个)jsonb数组包含至少一个项目的数组我传递，我设法找出一个工作查询

SELECT *
FROM TABLE T
WHERE (EXISTS (SELECT *
FROM JSONB_ARRAY_ELEMENTS_TEXT(T.DATA1) AS DATA1
WHERE ARRAY[DATA1] && ARRAY['some string','some other string']))
OR (EXISTS (SELECT *
FROM JSONB_ARRAY_ELEMENTS_TEXT(T.DATA2) AS DATA2
WHERE ARRAY[DATA2] && ARRAY['random string', 'another random string']));

但我认为这根本不是最佳的，我试图用交叉连接来做，但问题是，jsonb列中的数据1和数据2可以是一个空数组，然后连接将排除这些行，而也许其他jsonb列确实满足重叠&&条件。

我也尝试了其他方法，比如:

SELECT DISTINCT ID
FROM table,
JSONB_ARRAY_ELEMENTS_TEXT(data1) data1,
JSONB_ARRAY_ELEMENTS_TEXT(data2) data2
WHERE data1 in ('some string', 'some other string')
OR data2 in ('random string', 'string');

但是这个也不包括data1或data2为空字符串的行。所以我想到了FULL OUTER JOIN，但因为这是一个横向参考，它不起作用:The combining JOIN type must be INNER or LEFT for a LATERAL reference.

Answer 1

您不需要打开JSON数组。JSONB操作符?|可以直接做到这一点——它检查右侧参数的任何数组元素是否作为顶层元素包含在左侧JSON值中。

SELECT *
FROM the_table t
WHERE t.data1 ?| ARRAY['some string','some other string']))
OR t.data2 ?| ARRAY['random string', 'another random string']));

这不会返回行数组都空(或者两个列包含了搜索键)