Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL json array intersection query

I have a table with a jsonb data column, which looks like this:

data: {
    "categories": [
        "Category A",
        "Category D"
    ],
    "something": "dsa",
}

I would like to query rows that have one or more strings existing inside categories array (not empty intersection).

Let's suppose these strings to check against are "Category A" and "Category B".

How would such query look like?

Here's a query that does similar thing except it checks for all supplied strings to be existing in categories array:

SELECT *
FROM table
WHERE data->'categories' @> '["Category A", "Category B"]'

I need this query to match at least one string, not all.

like image 910
Patryk Avatar asked Aug 27 '26 03:08

Patryk


1 Answers

There is a ?| operator taking jsonb and text[] described as exists any:

select '["Category A", "Category D"]'::jsonb ?| array['Category A', 'Category B'];
 ?column? 
----------
 t

select '["Category A", "Category D"]'::jsonb ?| array['Category Ax', 'Category B'];
 ?column? 
----------
 f
like image 122
araqnid Avatar answered Aug 28 '26 18:08

araqnid



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!