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?
Remembering @> operator checks containment in a JSONB column, you can query on an array like {"x": ["a", "b", "c"]"} by just passing {"x": ["a"]} to the WHERE clause: 7. IN operator on attributes Sometimes, we may need to select items where the attributes inside a JSONB column matches a bunch of possible values. 8. Insert a whole object
select * from jsonbtest where to_json (array (select jsonb_array_elements (data) ->> 'id'))::jsonb ?| array ['1884595530', '791712670']; Unfortunately, you cannot index an expression, which has a sub-query in it. If you want to index it, you need to create a function for it:
Array slice operator is wonderful operator to extract selected items from Json. Taking the example of books, what if we want to retrieve every alternative book in the Json. To do that we will need the Array, Slice operator. Syntax of Array Slice operator is [StartIndex : EndIndex : Steps].
You can query with the @> operator on metadata. This operator can compare partial JSON strings against a JSONB column. It’s the containment operator. For this case you may need to add a GIN index on metadata column. 2. Select items by the value of a first level attribute (#2 way) The ->> operator gets a JSON object field as text.
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')
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)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With