How to Search in JSONB in PostgreSQL: 7 Query Examples with Indexing Tips for 2x Faster Results

JSONB in PostgreSQL is like a magic box. You can store flexible data. You can mix keys and values. You can keep nested objects. And the best part? You can search inside it—fast. If you know how.

TLDR: JSONB lets you store and search structured data inside PostgreSQL. Use operators like ->, ->>, and @> to query it. Add GIN indexes to speed things up, often 2x faster or more. Use the right index for the right query pattern.

Let’s break it down. Simple. Fun. Practical.

First, What Is JSONB?

JSONB stands for JSON Binary. It stores JSON in a decomposed binary format. That means:

Faster searches
Faster filtering
Index support
No duplicate keys

Imagine a table like this:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  data JSONB
);

The data column might look like:

{
  "name": "Laptop",
  "brand": "Dell",
  "price": 1200,
  "specs": {
    "ram": "16GB",
    "storage": "512GB"
  },
  "tags": ["electronics", "computers"]
}

Now let’s search it.

1. Search by Key with -> (Get JSON Object)

Use -> to get a JSON object field.

SELECT data -> 'brand' 
FROM products;

This returns JSON like:

"Dell"

It keeps the JSON format.

Best for: Getting raw JSON values.

2. Search by Key with ->> (Get Text Value)

Use ->> when you want plain text.

SELECT data ->> 'brand' 
FROM products;

This returns:

Dell

No quotes. Just text.

This is super useful in WHERE clauses:

SELECT *
FROM products
WHERE data ->> 'brand' = 'Dell';

Now we’re filtering.

3. Search Nested Fields

What about nested data? Like specs.ram?

You can chain operators:

SELECT *
FROM products
WHERE data -> 'specs' ->> 'ram' = '16GB';

That’s it. Clean and readable.

Pro tip: Always use ->> when comparing to text values.

4. Search with Containment Operator @>

This is where JSONB shines.

The @> operator checks if the JSON contains something.

SELECT *
FROM products
WHERE data @> '{"brand": "Dell"}';

This finds rows where brand is Dell.

It even works for nested fields:

SELECT *
FROM products
WHERE data @> '{"specs": {"ram": "16GB"}}';

Why this rocks:

Clean syntax
Powerful matching
Works great with GIN indexes

5. Search in JSON Arrays

Remember the tags array?

"tags": ["electronics", "computers"]

Search like this:

SELECT *
FROM products
WHERE data -> 'tags' ? 'electronics';

The ? operator checks if an array contains a value.

Super handy for categories, labels, roles, or permissions.

6. Search with jsonb_array_elements()

Need deeper array logic? Use jsonb_array_elements().

Example:

SELECT *
FROM products,
     jsonb_array_elements(data -> 'tags') AS tag
WHERE tag = '"electronics"';

This expands the array into rows.

Then filters.

It’s powerful. But slower without indexes.

7. Search with Path Queries (jsonb_path_query)

PostgreSQL supports JSONPath.

Example:

SELECT *
FROM products
WHERE jsonb_path_exists(data, '$.specs.ram ? (@ == "16GB")');

This feels like JavaScript.

It’s great for:

Complex conditions
Advanced filtering
Deep structures

But indexing becomes very important here.

How to Make JSONB Searches 2x Faster

Here’s the secret sauce: GIN indexes.

Without indexes? Slow scans.

With indexes? Lightning.

1. Basic GIN Index

CREATE INDEX idx_products_data
ON products
USING GIN (data);

This supports:

@>
?
Containment searches

Most common setup. Huge performance boost.

2. GIN Index with jsonb_path_ops

Smaller. Faster. More focused.

CREATE INDEX idx_products_data_path
ON products
USING GIN (data jsonb_path_ops);

This is optimized for @> containment only.

Pros:

Smaller index size
Faster containment queries

Cons:

Doesn’t support all operators

3. Expression Index for Specific Fields

If you often search by brand:

CREATE INDEX idx_products_brand
ON products ((data ->> 'brand'));

Now this runs much faster:

SELECT *
FROM products
WHERE data ->> 'brand' = 'Dell';

This is often 2x faster than a full JSONB scan.

Sometimes much more.

When Should You Use JSONB?

Use JSONB when:

Data structure changes often
You store semi-structured data
You ingest API responses
You build flexible configs

Don’t use it when:

You need strict schema enforcement
Every row has identical structure

In those cases, classic columns may be better.

Performance Tips Summary

Use @> when possible
Add a GIN index for containment queries
Use expression indexes for frequent key lookups
Avoid heavy jsonb_array_elements() without indexing
Test with EXPLAIN ANALYZE

Indexes are not optional at scale.

They make the difference between milliseconds and seconds.

Final Thoughts

JSONB turns PostgreSQL into a hybrid beast. Part relational. Part document database.

You can:

Store flexible JSON
Query nested fields
Search arrays
Index everything

And when you add the right GIN index?

Speed doubles. Sometimes more.

So experiment. Test your queries. Add indexes smartly.

JSONB is powerful. But only when you search it the right way.

Now go make your database fly.

🚀

WP Sticky