PostgreSQL / Advanced: evidence and reliable operations / Lesson 14

JSONB data and index strategies

Store flexible parcel metadata with typed relational boundaries. Distinguish json from jsonb, missing paths from JSON null, and operator-specific indexes.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Explain PostgreSQL JSON representations and extraction operators.
Choose scalar or GIN indexes from actual query operators.

JSONB is not a universal interchangeable binary format

PostgreSQL json preserves input text, including insignificant whitespace and object-key order. jsonb stores a decomposed representation, normalizes those presentation details and retains only the last duplicate object key. This is not MySQL’s binary JSON format, nor MariaDB’s JSON text alias. Use jsonb when its processing and indexing behavior fits the application, but preserve original payload text separately when exact byte-level provenance is required. Valid JSON syntax does not prove a required business field exists or has an acceptable type and range.

Choose JSON or text extraction deliberately

The -> operator returns a JSON value, while ->> returns text suitable for a deliberate scalar cast. This literal fixture extracts carrier Harbor and an exact numeric weight 2.50, then checks containment. A missing path can yield SQL NULL; JSON null is a JSON value and needs separate handling. Do not compare a text weight lexically and assume numeric ordering. An invalid cast should be handled as bad data, not hidden by discarding records from the report.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT '{"carrier":"Harbor","kg":2.5}'::jsonb ->> 'carrier' AS carrier,
 ('{"carrier":"Harbor","kg":2.5}'::jsonb ->> 'kg')::numeric(6,2) AS kg,
 '{"carrier":"Harbor","kg":2.5}'::jsonb @> '{"carrier":"Harbor"}'::jsonb AS matches;

Expected output

carrier | kg   | matches
Harbor  | 2.50 | true

Logical true may be displayed as t by psql. The values follow directly from the literal document.

Keep authoritative facts constrained

Identity, foreign keys and frequently joined financial facts usually deserve ordinary typed columns. A jsonb column can hold optional metadata without erasing the need for constraints and schema evolution. Add explicit validation for required fields and reject duplicate-key ambiguity before parsing when the input contract forbids it. jsonb normalization means a post-parse check cannot recover discarded duplicate source keys. Document whether a missing value, JSON null, SQL NULL and an empty string mean different business states.

Match the operator class to the query

A default GIN index on jsonb supports several documented containment and existence operators. jsonb_path_ops supports a narrower operator set with different storage and selectivity characteristics; it is not a drop-in promise for every ? existence query. The example proposes default GIN for document containment and a B-tree expression index for one frequently searched carrier scalar. Both are illustrative candidates on the training table. Choose between them from workload evidence, document size, mutation rate and the actual execution plan.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE INDEX ix_meta_document
ON quay_lab.parcel_meta USING gin (payload);
CREATE INDEX ix_meta_carrier
ON quay_lab.parcel_meta ((payload ->> 'carrier'));
EXPLAIN SELECT parcel_id FROM quay_lab.parcel_meta
WHERE payload @> '{"carrier":"Harbor"}'::jsonb;

The containment query can be eligible for the default GIN index, but the planner may prefer another path on a particular fixture.

Documents still participate in row concurrency

Updating a small path does not grant independent row locks to each JSON field: concurrent changes to one stored row still coordinate at row level. Large frequently rewritten documents can create write amplification, WAL and vacuum pressure. SQL/JSON capabilities have version-specific boundaries; JSON_TABLE was added in PostgreSQL 17, while jsonb itself dates to 9.4. Use the pinned manual for supported syntax and test data-level semantics during migration. A shared JSON label across products says nothing about on-disk compatibility or identical null and duplicate-key behavior.

Common errors and fixes

invalid input syntax for type numeric (22P02)

Why it happens: A JSON scalar extracted as text does not satisfy the expected numeric contract.
How to fix it: Validate source shape and type, then cast only accepted values.
Prevent it next time: Include wrong-type, missing and null JSON fields in ingestion tests.

A GIN index does not serve the expected operator

Why it happens: The chosen operator class does not support that search form or the predicate differs.
How to fix it: Inspect supported operators, indexed expression and actual plan.
Prevent it next time: Select index classes from query operators, not from the JSON label alone.

Original duplicate JSON keys disappear

Why it happens: jsonb normalization retained only the last duplicate value.
How to fix it: Reject ambiguous source documents before conversion or preserve original text separately.
Prevent it next time: Define provenance and duplicate-key rules at the ingestion boundary.

Check your understanding

1. A document contains weight "100" and another contains "9" as strings. Explain a correct numeric report.

Show worked solution

Text comparison can order the leading 1 before 9, so extract with ->>, validate that the field satisfies the numeric input contract and cast to a bounded numeric type. Decide how missing or invalid fields are reported instead of silently treating them as zero. A core authoritative weight may be clearer as a constrained numeric column.

2. Why is jsonb_path_ops not automatically the best index for a workload mixing containment and key-existence tests?