PostgreSQL: Filter by JSONB Field

If you’re storing data as JSONB in PostgreSQL and need to filter rows based on specific JSON field values, here’s what you need to know.

Quick Answer: Use the -» Operator

To filter JSONB columns, use the ->> operator to extract the field as text, then compare it:

SELECT
    emp_id,
    first_name,
    metadata ->> 'department' as department,
    metadata ->> 'level' as level
FROM employees
WHERE metadata ->> 'department' = 'Engineering'
ORDER BY first_name;

This returns:

emp_id | first_name | department   | level
-------+------------+---------------+--------
     7 | David      | Engineering   | Senior
     8 | Jennifer   | Engineering   | Senior
     9 | Richard    | Engineering   | Senior

That’s the solution. Now let me explain why this works and show you other approaches.

How It Works

The ->> operator extracts a value from the JSONB object and converts it to text. When you write metadata ->> 'department', you’re telling PostgreSQL: “grab the ‘department’ field from the JSON and give me back a plain text string.”

Once you have it as text, you can compare it like any regular column. No special syntax needed. This is why it’s the most straightforward approach for basic filtering.

Different Approaches & When to Use Them

Approach 1: Text Extraction with -» (Most Flexible)

Use ->> when you need flexibility in your conditions. It works for pattern matching, numeric comparisons after casting, and complex logic:

-- Pattern matching
WHERE metadata ->> 'name' LIKE '%John%'

-- Numeric comparison (must cast)
WHERE (metadata ->> 'years_experience')::INTEGER > 5

-- Multiple conditions
WHERE metadata ->> 'department' = 'Engineering'
  AND metadata ->> 'level' = 'Senior'

Best for: Everyday queries where you need flexibility.

Approach 2: Containment with @> (Best Performance)

The @> operator checks if JSONB contains a specific key-value pair. This one can use a GIN index, making it dramatically faster:

SELECT
    emp_id,
    first_name,
    metadata
FROM employees
WHERE metadata @> '{"department": "Engineering"}'::JSONB
ORDER BY first_name;

Same results, but on large tables, this can be 10-50x faster if you have a GIN index.

When should you use this? When you’re doing exact-match filtering and want maximum performance. Especially important if your table has thousands or millions of rows.

Approach 3: Check If Key Exists with ?

Sometimes you want to verify a key exists before comparing it. Use the ? operator:

SELECT
    emp_id,
    first_name,
    metadata
FROM employees
WHERE metadata ? 'level'  -- Check if 'level' key exists
  AND metadata ->> 'level' = 'Senior';

This is useful with inconsistent data where not all records have the same JSON fields.

Approach 4: Multiple Conditions at Once

If you need multiple fields to match specific values, you can check them all in one containment operation:

SELECT
    emp_id,
    first_name,
    metadata
FROM employees
WHERE metadata @> '{"department": "Engineering", "level": "Senior"}'::JSONB;

This checks that BOTH conditions exist in the JSON object. With the right index, it’s incredibly fast.

Real-World Example: User Preferences

Here’s a practical scenario you’ll hit often: filtering based on user settings. Find all users who have notifications enabled AND prefer dark theme:

SELECT
    emp_id,
    first_name,
    preferences ->> 'theme' as theme,
    preferences ->> 'notifications' as notifications
FROM employees
WHERE preferences @> '{"notifications": "enabled"}'::JSONB
  AND preferences ->> 'theme' = 'dark'
ORDER BY first_name;

This is the kind of query you see in SaaS apps for targeted functionality based on user preferences.

Performance: Add a GIN Index

If you’re filtering on JSONB columns regularly, create a GIN index. Without it, PostgreSQL scans every row. With it, queries jump straight to matches:

CREATE INDEX idx_metadata_gin ON employees USING GIN(metadata);

Now queries with the @> operator automatically use the index:

SELECT * FROM employees WHERE metadata @> '{"department": "Engineering"}';

Real-world impact: I’ve seen queries drop from 5+ seconds to under 100ms with the right index.

Decision Matrix: Which Approach to Use?

Scenario Use This Why
Extracting fields for display ->> Straightforward, readable
Exact match filtering on large tables @> + GIN index 10-50x faster
Pattern matching (LIKE) ->> Only option
Numeric comparison ->> (with cast) Flexibility needed
Check if key exists ? Handles missing fields
Multiple exact matches @> Single operation, index-friendly

Complex Filtering Patterns

As your queries get more sophisticated, here are patterns you’ll use frequently:

Multiple possible values:

WHERE (metadata ->> 'department' = 'Engineering'
       OR metadata ->> 'department' = 'Sales')
  AND metadata ->> 'level' IN ('Senior', 'Principal');

Numeric comparisons (always cast):

WHERE (metadata ->> 'years_experience')::INTEGER > 5;

Searching inside JSON arrays:

WHERE metadata -> 'skills' @> '["Python"]'::JSONB;

Nested JSON fields:

WHERE metadata -> 'address' ->> 'city' = 'New York';

FAQ

Q: What’s the difference between @> and -»? A: ->> extracts a value for comparison. @> checks if the JSON contains a key-value pair. More importantly, @> can use a GIN index, making it dramatically faster for large datasets.

Q: Do I really need to create a GIN index? A: If your table has more than a few thousand rows and you’re filtering on JSONB regularly, yes. The performance improvement is substantial. Small tables with occasional queries don’t need it.

Q: Can I filter on nested JSONB fields? A: Yes. Use the -> operator to traverse the structure, then ->> to extract the final value: WHERE metadata -> 'nested' ->> 'field' = 'value'. The -> keeps it as JSONB, the ->> extracts text.

Q: What if I want substring search in a JSONB field? A: Extract as text with ->>, then use LIKE: WHERE metadata ->> 'name' LIKE '%John%'. This is flexible but won’t use an index, so it’s slower on huge datasets.

Q: Can I filter JSONB arrays? A: Yes. Use @> to check if an array contains a value: WHERE metadata -> 'skills' @> '["Python"]'::JSONB. This is index-friendly.

Q: Which index type should I use for JSONB? A: GIN (Generalized Inverted Index) is the standard: CREATE INDEX idx ON table USING GIN(jsonb_col). It works with @>, ?, and other containment operators.

Q: Can I filter on multiple JSONB columns with one index? A: Create separate indexes on each column, or use a multi-column GIN index: CREATE INDEX idx ON table USING GIN(col1, col2).

Wrapping Up

Filtering JSONB in PostgreSQL comes down to choosing the right operator:

  • Use ->> for flexibility and everyday queries
  • Use @> with a GIN index for maximum performance on large datasets
  • Add indexes once filtering becomes a performance bottleneck

It’s that straightforward once you understand the trade-offs.