json - How to query jsonb arrays with IN operator

Question

I'm looking for a way to query postgres jsonb field with kind of "IN" clause inside an array.

Let's assume I have a table

CREATE TABLE test(
   id uuid,
   test_content jsonb,
   PRIMARY KEY(id)
);

INSERT INTO test (id, test_content) VALUES 
('aa82a8b8-33ef-4937-bd8c-8a4b40960f18', '[{"label":"a","label1":"1"},{"label":"b","label1":"2"}]'),
('ba82a8b8-33ef-4937-bd8c-8a4b40960f18', '[{"label":"c","label1":"3"}]'),
('da82a8b8-33ef-4937-bd8c-8a4b40960f18', '[{"label":"d","label1":"4"}]');

I need to select rows where label inside test_content's array might be b or d.

I tried

SELECT * 
FROM test 
WHERE test_content @> '[{"label":"b"}]' OR test_content @> '[{"label":"d"}]'

but when I want to extend my query with label1 containing 2 or 3 it gets complicated...

What I need is kind of WHERE label IN ('b','d') AND label1 IN ('2','3')

Is it possible with jsonb operators?

Answer 1

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.