JSONB array contains like OR and AND operators

Question

Consider a table temp (jsondata jsonb)

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

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

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%'

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%'

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

Are you sure you have any 'home' value that contains "foot" and begins with "stad"? — Joakim Danielson, Sep 19 '18 at 07:54
json looks like - {"properties":{"home":["football","stadium","16"]}} I want to perform and operation like - %foot% and %stad% on home array — jsanjayce, Sep 19 '18 at 08:04
But then you don't have _one_ value with both which you want when your using `AND` — Joakim Danielson, Sep 19 '18 at 08:27
Correct. That's why I am unable to perform this operation. I just tried this option. But, if there is some better way without using value, then that would be better as well. All, I want to achieve is to perform LIKE operation on JSONB array with OR and AND operations. — jsanjayce, Sep 19 '18 at 12:33

klin · Answer 1 · 2018-09-19T12:51:05.897

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)

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)

This I already tried. But, this involves join and group by which are heavy operations. So, I would prefer something which doesn't involve this operation. just using LIKE and AND operator. — jsanjayce, Sep 19 '18 at 12:31

Hitobat · Answer 2 · 2018-09-19T08:26:28.573

I have the following, but it was a bit fiddly. There's probably a better way but this is working I think.

The idea is to find the matching JSON array entries, then collect the results. In the join condition we check the "matches" array has the expected number of entries.

CREATE TABLE temp (jsondata jsonb);

INSERT INTO temp VALUES ('{"properties":{"home":["football","stadium",16]}}');

SELECT jsondata FROM temp t
INNER JOIN LATERAL (
    SELECT array_agg(value) AS matches
    FROM jsonb_array_elements_text(t.jsondata->'properties'->'home')
    WHERE value LIKE '%foo%' OR value LIKE '%sta%'
    LIMIT 1        
) l ON array_length(matches, 1) = 2;

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

S-Man · Answer 3 · 2018-09-19T08:43:24.497

demo: db<>fiddle

I would cast the array into text. Then you are able to search for keywords with every string operator.

Disadvantage: because it was an array the text contains characters like braces and commas. So it's not that simple to search for keyword with a certain beginning (ABC%): You always have to search like %ABC%

SELECT jsondata
FROM (
    SELECT 
        jsondata, 
        jsondata->'properties'->>'home' as a
    FROM 
        temp
)s
WHERE 
    a LIKE '%stad%' AND a LIKE '%foot%'

JSONB array contains like OR and AND operators

3 Answers3

Linked