Postgres jsonb array: query for non-empty intersection

Question

Suppose I have a JSONB column called value in a table t, and inside of these blobs of JSON is a tags field which is a list of strings.

I'd like to make a query for any of these JSON blobs tagged "foo" or "bar".

So suppose the table data looks like this:

value
---------------------
{"tags": ["other"]}
{"tags": ["foo", "quux"]}
{"tags": ["baz", "bar"]}
{"tags": ["bar", "foo"]}
{"tags": []}

I want to write some sort of query like this:

select value from t where value->'tags' NONEMPTY_INTERSECTION '["foo", "bar"]'

Such that the result will be:

value
-----------------------
{"tags": ["foo", "quux"]}
{"tags": ["baz", "bar"]}
{"tags": ["bar", "foo"]}

Is there an actual query that will accomplish this, and is there any way that it could possibly be fast?

Possible duplicate of querying JSONB with array fields

iLemming
– iLemming

2016-09-20 00:46:02 +00:00
Commented Sep 20, 2016 at 0:46 — iLemming
– iLemming, Commented Sep 20, 2016 at 0:46

Patrick · Accepted Answer · 2016-09-14 03:29:47Z

3

SELECT DISTINCT t.value
FROM t, jsonb_array_elements(t.value->'tags') tags
WHERE tags.value <@ '["foo", "bar"]'::jsonb;

answered Sep 14, 2016 at 3:29

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

Comments

Dan Burton · Accepted Answer · 2016-09-19 23:36:47Z

2

The operator I was looking for is ?|, which can be used like so:

select t.value from t where value->'tags' ?| array['foo','bar'];

Tested as follows:

danburton=# select * from jsonb_test;
           value
---------------------------
 {"tags": ["foo"]}
 {"tags": ["other"]}
 {"tags": ["foo", "quux"]}
 {"tags": ["baz", "bar"]}
 {"tags": ["bar", "foo"]}
 {"tags": []}
(6 rows)

danburton=# select jsonb_test.value from jsonb_test where value->'tags' ?| array['foo','bar'];
           value
---------------------------
 {"tags": ["foo"]}
 {"tags": ["foo", "quux"]}
 {"tags": ["baz", "bar"]}
 {"tags": ["bar", "foo"]}
(4 rows)

answered Sep 19, 2016 at 23:36

Dan Burton

53.9k28 gold badges122 silver badges207 bronze badges

Collectives™ on Stack Overflow

Postgres jsonb array: query for non-empty intersection

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related