How to Search Inside JSONB Array in PostgreSQL

To search inside a jsonb array in PostgreSQL, use the @> operator to check if the array contains a specific element or use jsonb_array_elements() to expand the array for detailed filtering. These methods let you find rows where the JSONB array matches your search criteria.

Syntax

PostgreSQL provides several ways to search inside a jsonb array:

• jsonb_column @> '[element]'::jsonb: Checks if the JSONB array contains the specified element.
• jsonb_array_elements(jsonb_column): Expands the JSONB array into a set of elements for filtering.

Here, @> means "contains", and jsonb_array_elements() helps to look at each item inside the array.

sql

SELECT * FROM table_name WHERE jsonb_column @> '["value"]'::jsonb;

SELECT * FROM table_name, jsonb_array_elements(jsonb_column) AS elem WHERE elem->> 'key' = 'value';

Example

This example shows how to find rows where the tags JSONB array contains the string "red", and how to filter rows where an object inside the array has a specific key-value pair.

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  tags JSONB
);

INSERT INTO products (name, tags) VALUES
('Apple', '["red", "fruit"]'),
('Banana', '["yellow", "fruit"]'),
('Car', '["blue", "vehicle"]'),
('Cherry', '[{"color": "red", "type": "fruit"}, {"color": "dark", "type": "fruit"}]');

-- Search for rows where tags array contains 'red'
SELECT id, name FROM products WHERE tags @> '["red"]';

-- Search for rows where tags array contains an object with color = 'red'
SELECT DISTINCT p.id, p.name
FROM products p, jsonb_array_elements(p.tags) AS elem
WHERE elem->> 'color' = 'red';

Output

 id |  name  
----+--------
  1 | Apple  
  4 | Cherry
(2 rows)

 id |  name  
----+--------
  4 | Cherry
(1 row)

Common Pitfalls

Common mistakes when searching inside jsonb arrays include:

• Using the @> operator with incorrect JSON syntax or data types.
• Trying to match objects inside arrays without expanding them first.
• Not casting strings or arrays properly to jsonb.

Always ensure your search value is valid JSON and matches the structure inside the array.

sql

/* Wrong: searching for string without JSON array brackets */
SELECT * FROM products WHERE tags @> '"red"'; -- This will fail

/* Right: use JSON array syntax */
SELECT * FROM products WHERE tags @> '["red"]';

Quick Reference

Operator/Function	Purpose	Example Usage
@>	Check if JSONB contains element	jsonb_column @> '["value"]'::jsonb
jsonb_array_elements()	Expand JSONB array to rows	jsonb_array_elements(jsonb_column)
->>	Get JSON object field as text	elem->> 'key' = 'value'
->	Get JSON object field as JSON	elem-> 'key'

Key Takeaways

Use the @> operator with a JSON array to check if a jsonb array contains a value.

Use jsonb_array_elements() to expand and filter individual elements inside a jsonb array.

Always write your search values as valid JSON and cast them to jsonb.

To search objects inside arrays, expand the array and filter by keys using ->> operator.

Common errors come from incorrect JSON syntax or missing array brackets in queries.