PostgreSQL JSONB Contains Operator (@>)

The @> operator in PostgreSQL tests whether a JSONB value contains another JSONB value. It's highly efficient when combined with GIN indexes and is essential for querying semi-structured data stored in JSONB columns.

Syntax

-- Check if left JSONB contains right JSONB
jsonb_column @> '{"key": "value"}'::JSONB

-- Also works with JSON literals
jsonb_column @> '{"key": "value"}'

-- Reverse: check if contained by
'{"key": "value"}'::JSONB <@ jsonb_column

Official Documentation

Example usage

-- Sample table setup
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    metadata JSONB
);

-- Create GIN index for fast containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- Check if JSON contains a key-value pair
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- Check for multiple key-value pairs
SELECT * FROM products
WHERE metadata @> '{"category": "electronics", "brand": "Apple"}';

-- Check if array contains element
SELECT * FROM products
WHERE metadata @> '{"tags": ["sale"]}';

-- Nested object containment
SELECT * FROM products
WHERE metadata @> '{"specs": {"warranty": "2 years"}}';

-- Check for key existence with any value
SELECT * FROM products
WHERE metadata ? 'discount';  -- Use ? operator for key existence

-- Find products with specific features array element
SELECT * FROM products
WHERE metadata -> 'features' @> '["waterproof"]';

-- Multiple conditions
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}'
  AND metadata @> '{"in_stock": true}';

-- Check array containment
SELECT * FROM users
WHERE tags @> '["premium", "verified"]'::JSONB;
-- Finds users with BOTH tags

-- Partial nested matching
SELECT * FROM orders
WHERE data @> '{
    "customer": {"country": "USA"},
    "status": "completed"
}';

-- Use in JOIN conditions
SELECT p.name, o.order_date
FROM products p
JOIN orders o ON o.data -> 'items' @> jsonb_build_array(
    jsonb_build_object('product_id', p.id)
);

-- Combine with other operators
SELECT * FROM products
WHERE metadata @> '{"category": "books"}'
  AND (metadata ->> 'price')::NUMERIC < 20;

-- Find records NOT containing value
SELECT * FROM products
WHERE NOT (metadata @> '{"discontinued": true}');

-- Check containment with OR conditions
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}'
   OR metadata @> '{"category": "computers"}';

-- Array of objects containment
SELECT * FROM orders
WHERE line_items @> '[{"product_id": 123}]'::JSONB;

Common issues

1. GIN index required: Without GIN index, containment queries can be slow on large tables.
2. Exact matching: @> requires exact matches. {"a": 1} does not contain {"a": "1"} (type matters).
3. Array order: For arrays, @> checks containment, not order. Use jsonb_array_elements for order-sensitive queries.
4. Partial key matching: @> doesn't support wildcard or partial key matching.

Best practices

1. Always create GIN index on JSONB columns used with @>: CREATE INDEX ON table USING GIN (jsonb_col).
2. Use @> for containment checks instead of expanding JSON with functions.
3. For key existence only, use ? operator: WHERE metadata ? 'key'.
4. Combine with regular indexes on extracted fields for complex queries.

Frequently Asked Questions

Q: What's the difference between @> and ->/->>?
A: The @> operator checks containment (does this JSON contain that structure?), while -> and ->> extract values. Use @> with GIN indexes for fast searches: WHERE data @> '{"status": "active"}' is faster than WHERE data ->> 'status' = 'active'.

Q: Do I need a special index for @> operator?
A: Yes, create a GIN index for good performance: CREATE INDEX idx_name ON table USING GIN (jsonb_column). Without it, PostgreSQL will perform a full table scan.

Q: Does @> work with arrays?
A: Yes, but it checks if all elements in the right array are present in the left array, regardless of order. '[1,2,3]' @> '[2,1]' is true. Element order doesn't matter.

Q: Can I search for partial text matches with @>?
A: No, @> requires exact matches. For partial text matching, extract the value with ->> and use LIKE: WHERE data ->> 'name' LIKE '%search%'.

Q: What's the reverse of @>?
A: The <@ operator: '{"a": 1}' <@ '{"a": 1, "b": 2}' returns true. It checks "is contained by" instead of "contains".