Jsonb Array Contains Like or and and Operators

Check if a Postgres JSON array contains a string

As of PostgreSQL 9.4, you can use the ? operator:

select info->>'name' from rabbits where (info->'food')::jsonb ? 'carrots';

You can even index the ? query on the "food" key if you switch to the jsonb type instead:

alter table rabbits alter info type jsonb using info::jsonb;
create index on rabbits using gin ((info->'food'));
select info->>'name' from rabbits where info->'food' ? 'carrots';

Of course, you probably don't have time for that as a full-time rabbit keeper.

Update: Here's a demonstration of the performance improvements on a table of 1,000,000 rabbits where each rabbit likes two foods and 10% of them like carrots:

d=# -- Postgres 9.3 solution
d=# explain analyze select info->>'name' from rabbits where exists (
d(# select 1 from json_array_elements(info->'food') as food
d(#   where food::text = '"carrots"'
d(# );
 Execution time: 3084.927 ms

d=# -- Postgres 9.4+ solution
d=# explain analyze select info->'name' from rabbits where (info->'food')::jsonb ? 'carrots';
 Execution time: 1255.501 ms

d=# alter table rabbits alter info type jsonb using info::jsonb;
d=# explain analyze select info->'name' from rabbits where info->'food' ? 'carrots';
 Execution time: 465.919 ms

d=# create index on rabbits using gin ((info->'food'));
d=# explain analyze select info->'name' from rabbits where info->'food' ? 'carrots';
 Execution time: 256.478 ms

LIKE query on elements of flat jsonb array

SELECT *
FROM   posts p
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements_text(p.tags) tag
   WHERE  tag LIKE '%TAG%'
   );

Related, with explanation:

• Search a JSON array for an object containing a value matching a pattern

Or simpler with the @? operator since Postgres 12 implemented SQL/JSON:

SELECT *
--     optional to show the matching item:
--   , jsonb_path_query_first(tags, '$[*] ? (@ like_regex "^ tag" flag "i")')
FROM   posts
WHERE  tags @? '$[*] ? (@ like_regex "TAG")';

The operator @? is just a wrapper around the function jsonb_path_exists(). So this is equivalent:

...
WHERE  jsonb_path_exists(tags, '$[*] ? (@ like_regex "TAG")');

Neither has index support. (May be added for the @? operator later, but not there in pg 13, yet). So those queries are slow for big tables. A normalized design, like Laurenz already suggested would be superior - with a trigram index:

• PostgreSQL LIKE query performance variations

For just prefix matching (LIKE 'TAG%', no leading wildcard), you could make it work with a full text index:

CREATE INDEX posts_tags_fts_gin_idx ON posts USING GIN (to_tsvector('simple', tags));

And a matching query:

SELECT *
FROM   posts p
WHERE  to_tsvector('simple', tags)  @@ 'TAG:*'::tsquery

Or use the english dictionary instead of simple (or whatever fits your case) if you want stemming for natural English language.

to_tsvector(json(b)) requires Postgres 10 or later.

Related:

• Get partial match from GIN indexed TSVECTOR column
• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

active-record postgres check if jsonb array contains x or y

The first thing to do is to get to the array using -> or #> and then you can use the ?| operator to check if the array overlaps what you're looking for. Something like this SQL:

preferences->'diet'->'foods' ?| array['apples', 'mangos']

or with ActiveRecord:

Person.where("preferences->'diet'->'foods' ?| array[:foods]", foods: ['apples', 'mangos'])

Postgres: check if array field contains a jsonb value with a specific property?

The schema is... problematic. You should really use jsonb instead of jsonb[] since json supports arrays out of the box.

Anyway, if the schema is as it is, then you can utilize something like this:

select distinct tmp2.id
from (
    select
        tmp1.id,
        jsonb_array_elements(tmp1.pets->'toys') as toys
    from (
        select
            id,
            unnest(pets) as pets
        from users
    ) tmp1
) tmp2
where jsonb_extract_path_text(tmp2.toys, 'color') = 'red'

(it can be probably written in a more readable way by utilizing with instead of nesting selects)

Explanation:

unnest will turn an internal array into separate rows.

jsonb_array_elements does the same thing, except it operates on jsonb arrays (we have to unwrap the internal toys arrays as well).

jsonb_extract_path_text retrieves the internal text stored under key.

Note that the query assumes the specific format you have. I didn't test the query against other json variants.

Postgres get all elements where value matches from jsonb array

If you want each matching object on a separate row, you can use jsonb_array_elements() to unnest the array of objects, then filter:

select o.obj
from data d
cross join lateral jsonb_array_elements(d.tags) o(obj)
where o.obj ->> 'name' = 'education'

That works in you have JSONB array (so the datatype of data is jsonb).

If, on the other hand, you have an array of json objects (so: jsonb[]), you can unnest instead:

select o.obj
from data d
cross join lateral unnest(d.tags) o(obj)
where o.obj ->> 'name' = 'education'

Note that this generates two rows when two objects match in the same array. If you want just one row, you can use exists instead:

select o.obj
from data d
where exists (
    select 1 from unnest(d.tags) o(obj) where o.obj ->> 'name' = 'education'
)

How to query jsonb arrays with IN operator

Short answer

You can use the function jsonb_array_elements() in a lateral join and use its result value in complex expressions in the WHERE clause:

SELECT t.* 
FROM test t
CROSS JOIN jsonb_array_elements(test_content)
WHERE value->>'label' IN ('b', 'd')
AND value->>'label1' IN ('2', '3')

Distinct

The query may return duplicated rows when the filter conditions are fulfilled in more than one element of the array in a single row, e.g.

SELECT t.* 
FROM test t
CROSS JOIN jsonb_array_elements(test_content)
WHERE value->>'label' IN ('a', 'b')

                  id                  |                          test_content                          
--------------------------------------+----------------------------------------------------------------
 aa82a8b8-33ef-4937-bd8c-8a4b40960f18 | [{"label": "a", "label1": "1"}, {"label": "b", "label1": "2"}]
 aa82a8b8-33ef-4937-bd8c-8a4b40960f18 | [{"label": "a", "label1": "1"}, {"label": "b", "label1": "2"}]
(2 rows)    

Hence it may be reasonable to use DISTINCT in the SELECT list:

SELECT DISTINCT t.* 
FROM test t
CROSS JOIN jsonb_array_elements(test_content)
WHERE value->>'label' IN ('a', 'b')

or EXISTS in the WHERE clause, which may be a bit faster:

SELECT t.*
FROM test t
WHERE EXISTS (
    SELECT 
    FROM jsonb_array_elements(test_content)
    WHERE value->>'label' IN ('a', 'b')
    )

You can also select matching array elements in cases where this information is needed:

SELECT id, value
FROM test t
CROSS JOIN jsonb_array_elements(test_content)
WHERE value->>'label' IN ('a', 'b')

                  id                  |             value             
--------------------------------------+-------------------------------
 aa82a8b8-33ef-4937-bd8c-8a4b40960f18 | {"label": "a", "label1": "1"}
 aa82a8b8-33ef-4937-bd8c-8a4b40960f18 | {"label": "b", "label1": "2"}
(2 rows)

Perfomance

The jsonb_array_elements() function is expensive. For larger tables the use of the function may be questionable due to heavy server load and the long execution time of a query.

While a GIN index can be used for queries with the @> operator:

CREATE INDEX ON test USING GIN (test_content)

in case of the function this is not possible. Queries supported by the index can be up to several dozen times faster than those using the function.

In postgresql how can I select rows where a jsonb array contains objects?

Using where exists with a filter on the unnested json array will return the rows with id 1, 2 & 3

SELECT * 
FROM mytable
WHERE EXISTS (
    SELECT TRUE 
    FROM jsonb_array_elements(data->'tags') x 
    WHERE x->>'name' IN ('tag2', 'tag3')
)

---

Related Topics