A PostgreSQL JSONB column can hold documents with different shapes, but efficient querying still requires a precise contract between the predicate and the index. A GIN index on the whole document supports particular JSONB operators. It does not automatically accelerate every expression that extracts a field from that document.

Match the operator to the index

The containment predicate payload @> '{"status":"pending"}' asks whether the document contains that key and JSON string value. With the default jsonb_ops operator class, a whole-column GIN index can support containment and top-level key existence, together with supported JSON path searches. The index stores searchable components; a bitmap heap scan may still visit the table and recheck candidate rows.

By contrast, payload->>'status' = 'pending' compares extracted text. A GIN index on payload is not an index on that text expression. A B-tree expression index on (payload->>'status') matches the scalar equality query. If many queries sort, join, or constrain status, an ordinary typed column may express the contract more clearly and avoid repeatedly deriving it from flexible documents.

Do not rewrite every text predicate to containment without checking types. The text extraction of a JSON number and a JSON string can produce the same text, while containment distinguishes their JSON types. A rewrite is equivalent only under the relevant data assumptions. Parameterize values through the database driver instead of constructing JSON or SQL by concatenating untrusted input.

An operator class is also a capability choice. jsonb_path_ops supports containment and supported path-match operators, but not the key-existence operators supplied by jsonb_ops. It can be smaller and more selective for suitable data, yet it is not a universally better replacement. Likewise, an index on a nested expression must match the expression actually queried; indexing the complete document does not imply every extracted subtree has its own index.

Missing and null are different questions

An absent key, a key whose value is JSON null, and a key containing the string “null” are distinct states. The existence operator detects the latter two as present. Extracting JSON with -> returns SQL NULL for a missing key, but a JSON null value for an explicit null. Extracting text with ->> maps both missing and JSON null to SQL NULL; the string remains text.

This affects filters, uniqueness assumptions, and application defaults. Use a key-existence check when presence matters and inspect JSON types when a field must be a string or number. SQL equality with NULL does not mean “is absent.” Also remember that the ? operator searches the top level; it does not recursively find a key nested anywhere in the document.

When it breaks

A common performance failure is installing GIN while leaving an incompatible extraction predicate unchanged. Another is assuming that an eligible index must be selected. Statistics, selectivity, table size, and estimated costs influence the plan; reading much of the table can make a sequential scan appropriate. Compare estimated and actual row counts before blaming the access method.

Large flexible documents also increase write work. GIN maintains many entries, and updating a document still updates a row with associated locking and storage costs. Index only the questions the workload actually asks. A valid JSONB value is not evidence that required business fields exist or have acceptable types.

Check it quickly

Save this as json-query.sql and run psql -X -v ON_ERROR_STOP=1 -f json-query.sql against a disposable PostgreSQL 18 database. The transaction creates temporary data and removes it on rollback. Only one percent of rows have pending status.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE documents (id integer, payload jsonb NOT NULL);
INSERT INTO documents SELECT g, jsonb_build_object('status',
  CASE WHEN g%100=0 THEN 'pending' ELSE 'done' END)
FROM generate_series(1,10000) AS g;
CREATE INDEX documents_payload ON documents USING gin(payload);
ANALYZE documents;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM documents WHERE payload @> '{"status":"pending"}';
EXPLAIN (COSTS OFF)
SELECT id FROM documents WHERE payload->>'status'='pending';
CREATE INDEX documents_status ON documents ((payload->>'status'));
ANALYZE documents;
EXPLAIN (COSTS OFF)
SELECT id FROM documents WHERE payload->>'status'='pending';
SELECT value, value ? 'x' AS has_key,
       value->'x' IS NULL AS sql_null_json, value->>'x' IS NULL AS sql_null_text
FROM (VALUES ('{}'::jsonb),('{"x":null}'::jsonb),('{"x":"null"}'::jsonb)) v(value);
DO $$ BEGIN
  IF (SELECT count(*) FROM documents WHERE payload @> '{"status":"pending"}') <> 100 THEN
    RAISE EXCEPTION 'wrong containment count';
  END IF;
  IF '{"n":1}'::jsonb @> '{"n":"1"}'::jsonb THEN
    RAISE EXCEPTION 'number and string were conflated';
  END IF;
  IF '{"outer":{"x":1}}'::jsonb ? 'x' THEN
    RAISE EXCEPTION 'top-level existence searched recursively';
  END IF;
  IF ('{"x":null}'::jsonb->'x') IS NULL THEN
    RAISE EXCEPTION 'JSON null confused with missing key';
  END IF;
END $$;
ROLLBACK;

On PostgreSQL 18.6, containment used a bitmap index scan through documents_payload and returned 100 rows. The text extraction predicate first used a sequential scan, then used documents_status after its expression index was created. These are observed choices for this distribution, not fixed plan guarantees or production latency estimates.

The final three rows show missing, explicit JSON null, and the text string separately. Their existence flags are false, true, true; their text-is-SQL-null flags are true, true, false. Assertions also check typed containment and nonrecursive existence. Keep these semantic checks when changing query shape: a faster plan is useful only if it still returns the intended documents.

Sources