Revision history [back]

To query an array of objects with JSONB in Postgres, you can use the JSONB functions and operators. Here are some examples:

1. Filter by a specific field in the objects:

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.

1. Filter by multiple fields in the objects:

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.

1. Filter by a specific value in the objects:

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.

1. Filter by a subquery on the objects:

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.