To query an array of objects with JSONB in Postgres, you can use the JSONB
functions and operators. Here are some examples:
SELECT *
FROM mytable
WHERE data->'myarray' @> '[{"field": "value"}]';
This query selects all rows from the mytable
table where the myarray
field is an array of objects that contain a field with the value value
.
SELECT *
FROM mytable
WHERE data->'myarray' @> '[{"field1": "value1", "field2": "value2"}]';
This query selects all rows from the mytable
table where the myarray
field is an array of objects that contain both fields with the values value1
and value2
.
SELECT *
FROM mytable
WHERE EXISTS (
SELECT 1
FROM jsonb_array_elements(data->'myarray') obj
WHERE obj->>'field' = 'value'
);
This query selects all rows from the mytable
table where the myarray
field is an array that contains at least one object with a field
value of value
.
SELECT *
FROM mytable
WHERE EXISTS (
SELECT 1
FROM jsonb_array_elements(data->'myarray') obj
WHERE obj->>'field' = 'value'
AND obj->>'otherfield' IN (
SELECT field
FROM othertable
WHERE condition
)
);
This query selects all rows from the mytable
table where the myarray
field is an array that contains at least one object with a field
value of value
, and where the same object also contains an otherfield
value that matches a subquery on another table.
Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss
Asked: 2021-11-07 11:00:00 +0000
Seen: 20 times
Last updated: Aug 23 '22
How can an array be filtered using the values in another array?
Can you define a lookup array in Excel by utilizing a value from a specific cell?
How can user input be added to an array using a loop in C# and then displayed through printing?
How can the objects that are duplicated within a nested array be removed?
How can I resolve the ValueError regarding incompatible shapes between (None, 20, 9) and (None, 9)?
What is a more effective method to verify the type of an Array containing elements of any type?
What is the way to add up items in an array with two dimensions using Google Apps Script?