PostgreSQL JSONB: query documents and choose an index

PostgreSQL JSONB stores JSON in a form you can query and index. Use -> when you need a JSON value, ->> when you need text, and @> when you want documents containing a particular structure. Choose the index after you know which expression and operator your query uses.

This guide follows four small order documents, then a separate 20,000-row fixture for index checks. We ran 23 checks with PostgreSQL 18.6 in a fresh local database. The examples verify results and observed query plans, without claiming a production speed improvement.

Keep row identity outside the document

Our table uses a regular primary-key column and a JSONB payload. The payload must be a nonnull JSON object, but this constraint does not validate every field inside it. One amount is deliberately a string, one note is JSON null, and another note is absent.

Run the SQL blocks in order in a disposable database. Stop if jsonb_lab already exists. Our PostgreSQL hosting guide explains the operating choices; this exercise uses a local server with invented data.

CREATE SCHEMA jsonb_lab;
CREATE TABLE jsonb_lab.orders (
    id bigint PRIMARY KEY,
    payload jsonb NOT NULL CHECK (jsonb_typeof(payload) = 'object')
);
INSERT INTO jsonb_lab.orders (id, payload) VALUES
(1, '{"status":"paid","amount":12,"customer":{"country":"FR"},"tags":["priority","export"],"note":null}'),
(2, '{"status":"pending","amount":2,"customer":{"country":"DE"},"tags":["standard"]}'),
(3, '{"status":"paid","amount":"12","customer":{"country":"FR"},"tags":[],"note":""}'),
(4, '{"status":"paid","amount":100,"customer":{"country":"US"},"tags":["priority"],"note":"ok"}');

The JSON types documentation describes the storage differences between json and jsonb. JSONB does not preserve the original whitespace, object-key order, or duplicate keys. In a separate check, converting an object with two x keys retained the final value, 2. If you must retain the exact incoming representation, preserve that representation separately.

Keeping an ID, tenant boundary, or frequently constrained business field in an ordinary typed column can make its rules easier to enforce. Flexible document fields still need an agreed meaning and validation policy.

Extract JSON and text deliberately

Compare the amount as JSON, as text, and by its JSON type. The path operator #>> extracts the nested country as text:

SELECT id,
       payload -> 'amount' AS json_value,
       payload ->> 'amount' AS text_value,
       jsonb_typeof(payload -> 'amount') AS json_type,
       payload #>> '{customer,country}' AS country
FROM jsonb_lab.orders
ORDER BY id;

Observed output in psql’s unaligned format:

1|12|12|number|FR
2|2|2|number|DE
3|"12"|12|string|FR
4|100|100|number|US

Rows 1 and 3 both yield text 12, but their JSON values differ: row 1 contains a number and row 3 a string. Our type check confirmed that -> returns SQL type jsonb, while ->> returns SQL type text. Once you have extracted text, the displayed value alone no longer tells you which of those JSON types it came from.

The JSON operators reference lists these operators and their result types. Use JSON extraction when you want to continue navigating a document; extract text when a text comparison or explicit conversion is what you intend.

Distinguish missing keys, JSON null, and empty strings

Checking only payload ->> 'note' IS NULL cannot tell our missing note from our explicit JSON null. Inspect key existence and the JSON value together:

SELECT id,
       payload ? 'note' AS key_exists,
       (payload -> 'note') IS NULL AS extracted_is_sql_null,
       (payload ->> 'note') IS NULL AS text_is_sql_null,
       COALESCE(jsonb_typeof(payload -> 'note'), 'missing') AS kind
FROM jsonb_lab.orders
ORDER BY id;

Observed output; t means true and f means false:

1|t|f|t|null
2|f|t|t|missing
3|t|f|f|string
4|t|f|f|string

Row 1 has a note key containing JSON null. JSON extraction returns that JSON value, so its SQL-null test is false, while text extraction returns SQL NULL. Row 2 has no note key: both extraction results are SQL NULL. Rows 3 and 4 contain strings, including the empty string in row 3.

The word missing in this output is our chosen display label for a SQL-null type result. It is not a PostgreSQL JSON type. The ? test also applies at the level you query: in our first document, payload ? 'country' was false, while (payload -> 'customer') ? 'country' was true.

Use containment for structured filters

Find paid orders whose tags include priority:

SELECT id
FROM jsonb_lab.orders
WHERE payload @> '{"status":"paid","tags":["priority"]}'::jsonb
ORDER BY id;

Observed IDs:

1
4

Row 1 also has an export tag; the extra tag does not prevent the match. Our separate array check confirmed that containment did not require matching order or repeated occurrences. Treat this as a membership-style filter, not a test that two arrays have the same sequence and counts.

Types still matter. A filter containing {"amount":12} matched only row 1. A filter containing {"amount":"12"} matched only row 3. Converting both amounts to text before comparing them would express a different rule.

Convert numeric values before ordering them

Ordering the extracted amount text put our rows in the order 4, 1, 3, 2, corresponding to text values 100, 12, 12, 2. That is not numeric order. An explicit conversion changes the comparison:

SELECT id, (payload ->> 'amount')::numeric AS amount
FROM jsonb_lab.orders
ORDER BY (payload ->> 'amount')::numeric, id;

Observed output:

2|2
1|12
3|12
4|100

The conversion accepted both the JSON number 12 and the JSON string "12" in this fixture. That may be too permissive for your data contract. We also tested the string "unknown": converting it to numeric raised SQLSTATE 22P02. Validate the field’s type and range before relying on a cast in queries or an index definition. For a required amount used throughout the application, consider a typed numeric column.

Match an expression index to a scalar query

Four rows are enough to understand the operators, but poor evidence for an index decision. Create a separate table with 20,000 documents, of which 20 have status flagged and a priority tag:

CREATE TABLE jsonb_lab.events (
    id bigint PRIMARY KEY,
    payload jsonb NOT NULL
);
INSERT INTO jsonb_lab.events
SELECT n,
       jsonb_build_object(
           'status', CASE WHEN n % 1000 = 0 THEN 'flagged' ELSE 'normal' END,
           'account', n,
           'tags', jsonb_build_array(CASE WHEN n % 1000 = 0 THEN 'priority' ELSE 'standard' END)
       )
FROM generate_series(1, 20000) AS g(n);
ANALYZE jsonb_lab.events;

Before adding document indexes, our status and tag queries both used sequential scans. For equality on the extracted status text, add a B-tree expression index:

CREATE INDEX events_status_idx
ON jsonb_lab.events ((payload ->> 'status'));
ANALYZE jsonb_lab.events;

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id FROM jsonb_lab.events
WHERE payload ->> 'status' = 'flagged';

In our run, the plan used an Index Scan on events_status_idx and returned 20 rows. The indexed expression matches payload ->> 'status' in the query. The index is on that text result, rather than on the whole JSONB document.

The expression-index documentation explains this approach and its maintenance costs. Test the expression your application actually sends. An index for text equality does not establish that a differently written containment query will use it.

Use GIN for the document-containment query

Now add a GIN index on the payload and query for the priority tag through containment:

CREATE INDEX events_payload_gin
ON jsonb_lab.events USING GIN (payload);
ANALYZE jsonb_lab.events;

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id FROM jsonb_lab.events
WHERE payload @> '{"tags":["priority"]}'::jsonb;

Our plan used a Bitmap Index Scan on events_payload_gin, followed by a Bitmap Heap Scan. It returned the same 20 expected IDs, from 1000 through 20000 in steps of 1000. Both main index checks used the default planner settings; we did not force their index selection.

A whole-document GIN index does not directly serve every expression involving the document. In a separate eligibility diagnostic, (payload -> 'tags') ? 'priority' did not use our whole-payload GIN index. We temporarily disabled sequential scans only for that diagnostic, inside a rolled-back transaction. That was an eligibility check, not a speed test or a setting to copy into production.

For this fixture, the expression index occupied 155,648 bytes and the GIN index 1,294,336 bytes. They support different queries and cover different data, so those sizes do not rank one index as universally better. Do not carry either size or the observed plan into a production estimate without measuring your documents and workload.

Choose from the query, then verify the plan

• Equality on one extracted field: consider a B-tree expression index matching that field’s expression.
• Containment across document fields: consider a GIN index that supports the operators you use.
• A different expression on a nested value: check its own index eligibility rather than assuming a whole-document index applies.
• Frequent numeric sorting or strict business rules: consider a typed column and ordinary relational constraints.

Read the actual plan and result count before drawing conclusions. Our PostgreSQL EXPLAIN guide covers plan interpretation and the fact that EXPLAIN ANALYZE executes the statement. A sequential scan can be a reasonable choice when a query reads much of a table; merely creating an index does not require the planner to select it.

These checks establish query semantics and index use on one synthetic dataset. They do not measure production latency, write overhead, concurrency, JSONPath, or the alternative jsonb_path_ops operator class. Before adding indexes to a live table, also plan the build’s operational impact.

Remove only the two disposable tables and their schema when finished:

DROP TABLE jsonb_lab.events;
DROP TABLE jsonb_lab.orders;
DROP SCHEMA jsonb_lab;

The runner confirmed the cleanup, stopped its local PostgreSQL server, and removed its temporary password file.