JSONB数组包含类似OR和AND运算符 [英] JSONB array contains like OR and AND operators

问题描述

考虑一个表temp (jsondata jsonb)

Postgres提供了一种使用

Postgres provides a way to query jsonb array object for contains check using

SELECT jsondata 
FROM temp 
WHERE (jsondata->'properties'->'home') ? 'football'

但是，我们不能对数组包含使用LIKE运算符.使数组中包含LIKE的一种方法是使用-

But, we can't use LIKE operator for array contains. One way to get LIKE in the array contains is using -

SELECT jsondata 
FROM temp,jsonb_array_elements_text(temp.jsondata->'properties'->'home') 
WHERE value like '%foot%'

与LIKE的OR操作可以通过-

OR operation with LIKE can be achieved by using -

SELECT DISTINCT jsondata 
FROM temp,jsonb_array_elements_text(temp.jsondata->'properties'->'home') 
WHERE value like '%foot%' OR value like 'stad%'

但是，我无法使用JSONB数组包含中的LIKE运算符执行AND操作.

But, I am unable to perform AND operation with LIKE operator in JSONB array contains.

推荐答案

用jsonb_array_elements()取消嵌套数组后，您可以检查满足以下条件之一的values并将它们按原始行分组，例如:

After unnesting the array with jsonb_array_elements() you can check values meeting one of the conditions and sum them in groups by original rows, example:

drop table if exists temp;
create table temp(id serial primary key, jsondata jsonb);
insert into temp (jsondata) values
    ('{"properties":{"home":["football","stadium","16"]}}'),
    ('{"properties":{"home":["football","player","16"]}}'),
    ('{"properties":{"home":["soccer","stadium","16"]}}');

select jsondata
from temp 
cross join jsonb_array_elements_text(temp.jsondata->'properties'->'home') 
group by jsondata 
-- or better:
-- group by id
having sum((value like '%foot%' or value like 'stad%')::int) = 2

                        jsondata                         
---------------------------------------------------------
 {"properties": {"home": ["football", "stadium", "16"]}}
(1 row)

---

更新.对于大型数据集，以上查询可能会很昂贵.有一个简化但更快的解决方案.您可以将数组转换为文本，然后将like应用于文本，例如:

---

Update. The above query may be expensive with a large dataset. There is a simplified but faster solution. You can cast the array to text and apply like to it, e.g.:

select jsondata
from temp 
where jsondata->'properties'->>'home' like all('{%foot%, %stad%}');

                        jsondata                         
---------------------------------------------------------
 {"properties": {"home": ["football", "stadium", "16"]}}
(1 row) 

这篇关于JSONB数组包含类似OR和AND运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！