PostgreSQL json array intersection query

Question

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.

Which version of PG? There are major differences for json types between 9.3 and 9.4. — Patrick
– Patrick, Commented Sep 28, 2015 at 0:42

Erwin Brandstetter · Accepted Answer · 2015-09-27 22:06:47Z

19

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

edited Sep 27, 2015 at 22:06

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

answered Sep 27, 2015 at 20:41

araqnid

135k25 gold badges163 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL json array intersection query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related