How to Search in JSONB in PostgreSQL

Emily Harris

20 hours ago

Storing and querying semi-structured data has become a central part of modern application development, and PostgreSQL’s JSONB data type offers a powerful way to handle it. Whether you are building APIs, managing dynamic configurations, or storing event logs, knowing how to efficiently search inside JSONB columns can dramatically improve performance and flexibility. PostgreSQL does not just store JSONB—it provides advanced operators, functions, and indexing strategies that make querying both precise and fast.

TLDR: PostgreSQL’s JSONB type allows you to store and efficiently query structured JSON data. You can search inside JSONB using operators like ->, ->>, @>, and functions such as jsonb_path_query. Proper indexing with GIN indexes significantly improves performance. Mastering these tools lets you combine relational and document-based querying in a single database system.

In this guide, we’ll explore how JSONB works, how to search inside it using different techniques, and how to optimize your queries for real-world use cases.

Understanding JSONB in PostgreSQL

PostgreSQL supports two JSON-related data types:

JSON – stores data as plain text
JSONB – stores data in a binary format

The key difference is that JSONB is parsed and stored in a decomposed binary format, which makes it significantly faster for searching and indexing. Unlike JSON, JSONB does not preserve whitespace, key order, or duplicate keys. In most applications, JSONB is the preferred option because of its superior querying abilities.

Here is a simple table example:

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

You might store data like this in the attributes column:

{
  "color": "red",
  "size": "M",
  "tags": ["sale", "summer"],
  "stock": 25
}

Now let’s explore how to search within this JSONB structure.

Basic JSONB Search Operators

PostgreSQL provides intuitive operators to extract and compare values inside JSONB data.

1. The -> Operator

The -> operator retrieves a JSON object field as JSON:

SELECT attributes -> 'color'
FROM products;

This returns the value including quotes (since it is JSON). Use this when you want to work with JSON objects rather than plain text.

2. The ->> Operator

The ->> operator extracts a JSON field as text:

SELECT attributes ->> 'color'
FROM products;

This is especially useful for filtering:

SELECT *
FROM products
WHERE attributes ->> 'color' = 'red';

This query returns all products where the color attribute is “red”.

Searching Nested JSONB Values

JSON data often contains nested structures. PostgreSQL makes it easy to traverse multiple levels.

For example:

{
  "details": {
    "manufacturer": "Acme",
    "warranty": {
      "years": 2,
      "type": "limited"
    }
  }
}

You can access nested keys using chained operators:

SELECT attributes -> 'details' -> 'manufacturer'
FROM products;

Or retrieve nested text values:

SELECT attributes -> 'details' ->> 'manufacturer'
FROM products;

For deeply nested fields:

SELECT attributes -> 'details' -> 'warranty' ->> 'years'
FROM products;

This approach works well for structured documents with predictable schemas.

Using the Containment Operator (@>)

One of JSONB’s most powerful search features is the containment operator @>. It checks whether the left JSONB value contains the right JSONB value.

Example:

SELECT *
FROM products
WHERE attributes @> '{"color": "red"}';

This query returns all rows where the attributes contain color = red.

You can also check arrays:

SELECT *
FROM products
WHERE attributes @> '{"tags": ["sale"]}';

This is extremely useful for tag filtering and flexible attribute matching.

Working with Arrays in JSONB

Arrays are common in JSON structures. PostgreSQL allows you to search within JSONB arrays efficiently.

Example data:

{
  "tags": ["sale", "clearance", "winter"]
}

To check if a value exists in the array:

SELECT *
FROM products
WHERE attributes -> 'tags' ? 'sale';

The ? operator checks if a key or array element exists.

Other useful operators:

?| – checks if any of multiple values exist
?& – checks if all specified values exist

Example with multiple tags:

WHERE attributes -> 'tags' ?| array['sale', 'discount'];

JSONPath Queries (Advanced Searching)

Starting from PostgreSQL 12, you can use JSONPath expressions for more advanced querying.

JSONPath allows complex conditions similar to XPath for XML.

Example:

SELECT *
FROM products
WHERE jsonb_path_exists(attributes, '$.stock ? (@ > 10)');

This query selects products where stock is greater than 10.

You can filter nested values:

SELECT *
FROM products
WHERE jsonb_path_exists(
  attributes,
  '$.details.warranty.years ? (@ >= 2)'
);

JSONPath is particularly helpful when:

You need conditional logic inside JSON
Your data structure varies across rows
You require expressive filtering rules

Although powerful, JSONPath queries can be slower without proper indexing.

Indexing JSONB for Fast Searches

Searching JSONB fields without indexing can be slow for large datasets. Fortunately, PostgreSQL supports several indexing strategies.

1. GIN Index

The most common and recommended index type for JSONB is GIN (Generalized Inverted Index).

CREATE INDEX idx_products_attributes
ON products
USING GIN (attributes);

This dramatically improves performance for:

The @> containment operator
Key existence operators like ?
Array element searches

2. Expression Index

If you frequently search for a specific key, you can create an index directly on that expression:

CREATE INDEX idx_products_color
ON products ((attributes ->> 'color'));

This makes equality comparisons like WHERE attributes ->> 'color' = 'red' much faster.

Performance Considerations

While JSONB is powerful, it is important to use it wisely.

Best practices include:

Use JSONB for semi-structured or flexible data
Avoid storing highly relational data inside JSONB unnecessarily
Create GIN indexes for large datasets
Use expression indexes for frequently filtered keys
Measure performance using EXPLAIN ANALYZE

If your queries repeatedly extract the same JSON key, consider normalizing that field into a dedicated column. This hybrid approach combines relational strength with document flexibility.

Combining Relational and JSONB Queries

One of PostgreSQL’s biggest advantages is the ability to mix relational columns and JSONB filtering in the same query.

For example:

SELECT *
FROM products
WHERE name ILIKE '%shirt%'
AND attributes ->> 'color' = 'blue'
AND attributes @> '{"tags": ["summer"]}';

This combines:

Traditional text pattern matching
JSON field extraction
Array containment checking

The result is a powerful and flexible query that would typically require a separate document database.

Common Use Cases for JSONB Searching

JSONB searching is especially valuable in the following scenarios:

E-commerce platforms: dynamic product attributes
Event logging systems: unstructured event data
Configuration storage: flexible application settings
User profiles: optional or evolving fields

Instead of altering your table schema for every small change, JSONB allows you to adapt quickly while still querying efficiently.

Final Thoughts

Searching in JSONB in PostgreSQL is both practical and powerful. With operators like ->, ->>, and @>, along with array checks and JSONPath expressions, you can perform highly specific queries on structured documents. When paired with proper indexing—especially GIN indexes—performance remains strong even at scale.

What makes JSONB particularly attractive is the blend of flexibility and relational reliability. You are not choosing between a SQL database and a document store—you get the best of both worlds. By mastering JSONB search techniques, you unlock a more dynamic and adaptable PostgreSQL backend, ready to handle the evolving data demands of modern applications.