PostgreSQL’s jsonb type is incredibly useful when you need flexible, semi-structured data. But a common question eventually comes up:

If I store a value inside JSONB, how do I sort by it efficiently?

For example, suppose we have:

CREATE TABLE users (
    id bigint,
    data jsonb
);

And the data column contains:

{
  "name": "Alice",
  "age": 30,
  "created_at": "2026-08-01T12:30:00Z"
}

We can sort by age like this:

SELECT *
FROM users
ORDER BY (data->>'age')::int;

This works perfectly. But on a large table, PostgreSQL may have to extract and cast the JSON value for every row and then perform a sort.

That’s where an expression index comes in.

The Right Index for JSONB Sorting

If your query sorts by:

ORDER BY (data->>'age')::int

you can create a B-tree index on exactly that expression:

CREATE INDEX users_age_idx
ON users (((data->>'age')::int));

Now PostgreSQL has an ordered index containing the extracted integer values.

This is fundamentally different from indexing the JSONB column itself.

Why a GIN Index Isn’t the Answer

You may already know that PostgreSQL supports GIN indexes for JSONB:

CREATE INDEX users_data_idx
ON users USING GIN (data);

GIN is excellent for queries such as:

SELECT *
FROM users
WHERE data @> '{"age": 30}';

It is designed for efficiently finding rows based on the contents of complex JSON structures.

But GIN isn’t an ordered index.

If your important operation is:

ORDER BY (data->>'age')::int

a GIN index on data generally won’t help PostgreSQL produce the rows in sorted order.

For ordering, a B-tree expression index is usually what you want.

The Expression Must Match

One of the most important details is that the index should represent the expression you’re actually using.

Suppose you create:

CREATE INDEX users_age_idx
ON users ((data->>'age'));

but your query is:

ORDER BY (data->>'age')::int;

Those are not equivalent from the index’s perspective.

The first expression produces text; the second produces integer.

If age is numeric data, index the numeric expression:

CREATE INDEX users_age_idx
ON users (((data->>'age')::int));

Then use the same expression in your query:

SELECT *
FROM users
ORDER BY (data->>'age')::int;

This is particularly important because sorting JSON values as text can produce surprising results.

For example, text sorting gives:

1
100
20
3

whereas numeric sorting gives:

1
3
20
100

JSONB Timestamps

A particularly common use case is storing timestamps inside JSONB.

For example:

{
  "event": "signup",
  "created_at": "2026-08-01T12:30:00Z"
}

You might write:

SELECT *
FROM events
ORDER BY (data->>'created_at')::timestamptz DESC;

Create an expression index:

CREATE INDEX events_created_at_idx
ON events (((data->>'created_at')::timestamptz));

Now PostgreSQL has an ordered representation of that timestamp expression.

This can be especially valuable for queries such as:

SELECT *
FROM events
ORDER BY (data->>'created_at')::timestamptz DESC
LIMIT 50;

Instead of sorting every matching row, PostgreSQL may be able to walk the index and retrieve the first 50 rows directly.

That combination of ORDER BY + LIMIT is where an appropriate index can be particularly powerful.

What About DESC?

You might see people create the index like this:

CREATE INDEX events_created_at_idx
ON events (((data->>'created_at')::timestamptz) DESC);

That’s valid, but you generally don’t need to do this just because your query uses DESC.

PostgreSQL B-tree indexes can be scanned in either direction.

So this:

CREATE INDEX events_created_at_idx
ON events (((data->>'created_at')::timestamptz));

can generally support both:

ORDER BY (data->>'created_at')::timestamptz ASC

and:

ORDER BY (data->>'created_at')::timestamptz DESC

Combining Filtering and Sorting

Things get more interesting when you have both a WHERE clause and an ORDER BY.

Suppose your JSON contains:

{
  "status": "active",
  "score": 87
}

and your query is:

SELECT *
FROM users
WHERE data->>'status' = 'active'
ORDER BY (data->>'score')::int DESC
LIMIT 50;

A simple index on score may help with the ordering, but a composite expression index can sometimes be substantially better:

CREATE INDEX users_status_score_idx
ON users (
    (data->>'status'),
    ((data->>'score')::int)
);

Now the index is organized first by status and then by score.

This can allow PostgreSQL to efficiently locate the active portion of the index and retrieve those rows in score order.

The exact best index depends on the query and data distribution, so don’t blindly add composite indexes. Check the execution plan.

Always Check EXPLAIN

The best way to determine whether PostgreSQL is benefiting from your index is:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM events
ORDER BY (data->>'created_at')::timestamptz DESC
LIMIT 50;

Without a useful index, you might see a plan involving a sort:

Sort
  Sort Key: ...
  -> Seq Scan on events

With an appropriate index, PostgreSQL may instead use an index scan:

Limit
  -> Index Scan using events_created_at_idx on events

The exact plan will depend on the table size, statistics, selectivity, cost settings, and other factors.

An index existing on disk does not guarantee PostgreSQL will use it.

Missing JSON Values

JSONB introduces another consideration: not every row necessarily has the field.

For example:

{"name": "Alice"}

might not contain age at all.

In that case:

data->>'age'

returns NULL.

You can control how those values appear in your ordering:

ORDER BY (data->>'age')::int NULLS LAST;

If you’re designing an expression index specifically around a query with particular NULL behavior, make sure the index definition and query are compatible with the ordering you need.

Partial Indexes Can Help

If only a subset of your rows needs to be sorted this way, a partial index can reduce index size.

For example, if only active users matter:

CREATE INDEX users_active_score_idx
ON users (((data->>'score')::int))
WHERE data->>'status' = 'active';

This can be much smaller than indexing every row.

The corresponding query is:

SELECT *
FROM users
WHERE data->>'status' = 'active'
ORDER BY (data->>'score')::int DESC
LIMIT 50;

Partial indexes can be particularly attractive for large tables where only a relatively small subset of rows participates in the query.

Should You Store It Outside JSONB?

There’s another important architectural question:

If I frequently filter or sort by this value, should it really be in JSONB?

If created_at, status, user_id, or score is central to your application’s queries, putting it in a normal PostgreSQL column may be simpler:

CREATE TABLE events (
    id bigint,
    created_at timestamptz,
    status text,
    data jsonb
);

Then:

CREATE INDEX events_created_at_idx
ON events (created_at);

and:

SELECT *
FROM events
ORDER BY created_at DESC
LIMIT 50;

This avoids repeatedly extracting and casting the value from JSONB.

JSONB is excellent for data that is genuinely flexible or semi-structured. But if a field has become a fundamental part of your relational model and query patterns, a normal column is often the better choice.

The Rule of Thumb

A useful mental model is:

• Need to search inside arbitrary JSONB? Consider a GIN index.
• Need to sort by a JSONB value? Consider a B-tree expression index.
• Need to filter and sort? Consider a composite expression index.
• Only a subset of rows matters? Consider a partial expression index.
• A JSONB field is queried constantly? Consider promoting it to a normal column.

For example:

CREATE INDEX events_created_at_idx
ON events (((data->>'created_at')::timestamptz));

pairs naturally with:

SELECT *
FROM events
ORDER BY (data->>'created_at')::timestamptz DESC
LIMIT 50;

And for more complicated queries, EXPLAIN (ANALYZE, BUFFERS) should be the final authority.

The key idea is simple: GIN indexes are great at finding things inside JSONB; B-tree expression indexes are what you generally want when you need those extracted values to be ordered.