PostgreSQL JSON and structured alternatives

What belongs in a JSON document

A document groups facts whose structure and changes belong together. A catalogue editor may read and replace a product description as one piece, with different fields for different products. JSONB can hold that variable structure. It does not make the contents free of a schema: readers still expect particular names, types and meanings.

One design puts specialised attributes in typed detail rows, such as a teaware row with a capacity column. Another puts a product’s variable attributes in a JSONB document row. In either design, keep the existing product row as the owner of product identity, price and stock.

Both alternatives can give each product at most one detail row by making product_id its primary key and a foreign key to products. The JSON object does not need to repeat that identity inside itself. The reference still names the same P7 that order lines name.

Typed columns make a stable field’s type visible to every query. A capacity_ml numeric NOT NULL CHECK (capacity_ml > 0) column states a positive-capacity contract directly. A specialised teaware_details row keeps that field away from tea-only attributes. Neither its foreign key nor its primary key requires every teaware product to have a detail row; subtype completeness needs a separate rule.

P7’s gift and ceramic memberships have a different shape. They can be added separately, made unique as pairs and referenced through ordinary keys. Keep related rows when those independent relationships matter. An array of tag names inside a document does not give each membership its own row identity or an ordinary foreign key to the tag table. Relational data modelling develops that judgement, including the cost of a generic attribute table.

Arrays and composite values

JSON is not the only way to store a value with parts. PostgreSQL’s text[] keeps a sequence of text values with one element type. A composite type names its fields and their SQL types. For a stable amount-and-unit value, this is possible:

CREATE TYPE measurement AS (
  amount numeric,
  unit text
);
SELECT ROW(300, 'ml')::measurement;

The composite definition supplies types, not NOT NULL, positive amounts or permitted units. Those need constraints, for example on the containing table or an appropriate domain. Likewise, declaring text[2] does not enforce an array length of two in PostgreSQL. Choose arrays for homogeneous sequences and composites for stable named parts; neither automatically supplies the identity, references or independent update boundary of related rows. Values, missing data and equality explains when the parts need separate meanings.

JSON and JSONB

json stores the input JSON text, preserving whitespace, object-key order and duplicate keys. jsonb converts it into a representation that can be processed without reparsing that text and supports native indexes. It discards that formatting and object-key order; if a key repeats, the last value survives.

SELECT '{ "capacity_ml": 300, "capacity_ml": 330 }'::jsonb;
-- {"capacity_ml": 330}

Prefer JSONB for documents you will query. Retain the original incoming body separately when its exact representation matters, such as signature verification or an audit of what arrived. Even json stores decoded database text, rather than every byte and header of a network message. Reject duplicate keys before JSONB conversion if their presence is an error: after conversion, they are gone.

Both JSON types validate JSON syntax. Neither requires an object: 300, "large", [] and null are valid JSON values. JSONB also has representation limits, including PostgreSQL’s numeric range and rejection of the zero Unicode character. A successful conversion establishes neither an application document shape nor a valid capacity.

For the next examples, the proposed detail table has an explicit product reference and requires an object. It permits different members for different products:

CREATE TABLE product_documents (
  product_id text PRIMARY KEY REFERENCES products,
  details jsonb NOT NULL,
  CHECK (jsonb_typeof(details) = 'object')
);
INSERT INTO product_documents VALUES
  ('P3', '{"origin":"Japan"}'),
  ('P7', '{"capacity_ml":300}');

Reading values and missing fields

details -> 'capacity_ml' extracts a member as JSONB. details ->> 'capacity_ml' extracts its text. For numeric 300, both displays can look like 300, but the text no longer says whether it came from a number or a string.

Numeric 300 and the JSON string “300” both extract as text 300. Preserving JSON distinguishes them as a number and a string. That distinction matters when a stored capacity must be a JSON number rather than merely text that can be converted.

{"capacity_ml":300}

300

300

JSON null is a value inside JSON. SQL NULL marks absent SQL information. A JSON-preserving extraction retains the former; text extraction turns it into SQL NULL. A missing member, an unsuitable structure and a SQL-null document also give SQL NULL through these extraction operators. The same text result can therefore hide several different inputs.

SELECT details ? 'capacity_ml' AS has_member,
       jsonb_typeof(details -> 'capacity_ml') AS json_type,
       details ->> 'capacity_ml' AS capacity_text
FROM product_documents
WHERE product_id = 'P7';
-- true | number | 300

Use ? here only after establishing that details is an object: it tests top-level keys or string array elements. jsonb_typeof returns the text 'null' for JSON null, but SQL NULL for an absent extraction. For deeper paths, #> '{dimensions,capacity_ml}' preserves JSON and #>> extracts text.

Extraction is also a comparison choice. Under COLLATE "C", text '1000' sorts before '300'. That cannot answer “Which capacity is larger?” Cast a validated numeric member to numeric before comparing or ordering it. Casting alone is insufficient validation: both numeric 300 and string “300” can become numeric 300, while string “large” causes a conversion error.

Validating a document

Suppose the shop now requires every row in a proposed teaware_documents table to contain a positive JSON-number capacity in millilitres. This is a stronger local rule than the flexible product-document table above. P7 satisfies it; P3’s origin-only document belongs elsewhere.

Write down all four parts of that contract: the document is an object, the member exists, its JSON type is number, and its numeric value is positive. The following definition allows extra members while enforcing those requirements:

CREATE TABLE teaware_documents (
  product_id text PRIMARY KEY REFERENCES products,
  details jsonb NOT NULL,
  CHECK (jsonb_typeof(details) = 'object'),
  CHECK (details ? 'capacity_ml'),
  CHECK (CASE
    WHEN jsonb_typeof(details -> 'capacity_ml') = 'number'
      THEN (details ->> 'capacity_ml')::numeric > 0
    ELSE false
  END)
);
INSERT INTO teaware_documents VALUES
  ('P7', '{"capacity_ml":300}');

The CASE matters twice. It returns false for a missing, null or wrong-type member, and it performs the cast only in the numeric branch. Do not depend on the left-to-right order of conditions joined by AND to protect an unsafe cast.

A weaker CHECK ((details ->> 'capacity_ml')::numeric > 0) accepts {}: extraction and comparison produce unknown, and a PostgreSQL check accepts true or unknown. It also accepts {"capacity_ml":"300"}, losing the intended JSON-type rule. Missing members, JSON null, zero, numeric strings and non-numeric strings test different parts of this contract. The explicit contract rejects them without trying to cast “large”.

The longer definition buys flexibility for other document members, while accepting responsibility for this one field’s schema. Compare it with a typed teaware_details row containing capacity_ml numeric NOT NULL CHECK (capacity_ml > 0). If most members acquire stable types, requiredness and separate query uses, columns can express that growing contract more directly. Keys and foreign keys still govern relationships; a check over one JSON value is not a replacement for them. Constraints and data integrity develops enforcement across the wider schema.

Containment, existence and JSON paths

Choosing the operator starts with the question. “Does this object have a key?”, “Does this array include these elements?” and “Is this the whole document?” are different tests.

P7 already has the tags gift and ceramic as independent memberships. To compare the JSON operators, form a diagnostic document whose tags array is ["gift", "ceramic"], then consider reversed order, an added tag and a repeated gift. These are search examples in an isolated tag_probe(label, doc jsonb) table; the membership rows remain authoritative. The same documents can satisfy containment while failing whole-value equality.

doc ? 'gift'

doc ? 'gift' does not descend into tags. (doc -> 'tags') ? 'gift' applies existence to the extracted array instead. Containment, @>, takes another JSONB value and asks whether that structure fits inside the document. {"tags":["gift"]} preserves the nesting needed for this search.

Extra object members and array elements are allowed by containment. Within arrays, order and repeated occurrences do not matter to containment. Whole-value JSONB equality still distinguishes the reversed, extended and repeated arrays, although rearranging an object’s keys would not change equality. Use an explicit count or occurrence model when “two gifts” means two occurrences: repeating “gift” in a containment operand does not demand two.

JSONPath filters

JSONPath describes a route through a JSON value. $ is the document root; @ is the item currently being tested inside a filter. This search asks whether a numeric capacity of at least 300 occurs:

SELECT product_id FROM product_documents
WHERE details @? '$.capacity_ml ? (@ >= 300)';
-- P7

@? asks whether the path produces any item. @@ instead evaluates a JSONPath predicate, such as details @@ '$.capacity_ml >= 300'. They are different expression forms; do not wrap a Boolean predicate in @? and assume it tests that Boolean’s truth.

The default lax mode can adapt structure, including unwrapping arrays. A diagnostic {"capacity_ml":[300]} can therefore pass the search above, although it fails our stored-number contract. The match operators also suppress missing-member, unexpected-type, numeric and datetime errors. That makes heterogeneous-document searches useful; it does not make a successful search a validation certificate. A strict path changes structural adaptation, but the match operators’ error suppression still needs accounting for.

Turning JSON into rows, and rows into JSON

Use native expansion when a document contains a list that SQL needs to inspect. jsonb_array_elements returns one row per element; WITH ORDINALITY retains its one-based occurrence number:

SELECT p.label, e.position, e.tag
FROM tag_probe AS p
CROSS JOIN LATERAL
  jsonb_array_elements(p.doc -> 'tags')
  WITH ORDINALITY AS e(tag, position)
WHERE p.label = 'repeated'
ORDER BY e.position;
-- repeated | 1 | "gift"
-- repeated | 2 | "ceramic"
-- repeated | 3 | "gift"

The document label and position identify an occurrence in this result. The two gifts remain two rows. PostgreSQL also offers jsonb_to_recordset and JSON_TABLE to project document fields into declared SQL column types. Projection and conversion do not install constraints on the stored document; converting a numeric-looking string can erase exactly the distinction we checked earlier.

The other direction separates an API’s shape from storage. Order 103 already has numbered relational lines. Build its response from those rows, with the agreed unit prices:

SELECT jsonb_build_object(
  'order_id', o.order_id,
  'status', o.status,
  'lines', COALESCE((
    SELECT jsonb_agg(jsonb_build_object(
      'line_no', i.line_no, 'product_id', i.product_id,
      'quantity', i.quantity, 'unit_price', i.unit_price
    ) ORDER BY i.line_no)
    FROM order_items AS i
    WHERE i.order_id = o.order_id
  ), '[]'::jsonb)
) AS response
FROM orders AS o
WHERE o.order_id = 103;

The response contains pending order 103, line 1 with P7 × 1 at 20.00, and line 2 with P3 × 2 at 4.50. The lines still total 29.00. Ordering inside jsonb_agg fixes their array order; COALESCE supplies an empty array for an order without lines. Nothing requires storing the response as the order. Joins and the meaning of a result row and Aggregates and window functions develop those result-level choices.

Indexing the question being asked

An index must match the expression and operator the query actually uses. Whole-document containment, existence within an extracted tags array and numeric capacity ordering ask different questions of different left operands. A suitable index for one need not serve the others.

JSONB GIN operator classes

The default jsonb_ops GIN class supports ?, ?|, ?&, @>, @? and @@. jsonb_path_ops supports the last three, without the key-existence operators.

-- Alternatives to compare, rather than a reason to keep both:
CREATE INDEX tag_probe_doc_ops ON tag_probe USING gin (doc);
CREATE INDEX tag_probe_doc_paths ON tag_probe
  USING gin (doc jsonb_path_ops);

SELECT label FROM tag_probe
WHERE doc @> '{"tags":["gift"]}'::jsonb;

The default class retains separate keys and values. The path class combines a value with the keys leading to it in a hash entry. That can make a path-and-value search more specific and its index smaller, but it cannot answer whether a key exists independently of a value. An empty structure such as {"a":{}} provides no value entry for the path class; searching for it can require a full index scan. Choose from the searches that occur, rather than treating one class as universally better.

JSONPath support also has a limit. GIN extracts useful equality clauses of the form a member route equals a constant. Supporting @? does not mean a pure >= 300 path filter supplies selective index keys. Nor does GIN provide capacity order or reconstruct the whole document for an index-only result. PostgreSQL index methods and operators explains candidates, rechecks and bitmap precision.

Indexes on JSON expressions

A GIN on doc fits whole-document containment. For the extracted-array existence search, index that extracted value instead:

CREATE INDEX tag_probe_tags ON tag_probe
USING gin ((doc -> 'tags'));

SELECT label FROM tag_probe
WHERE (doc -> 'tags') ? 'gift';

That targeted index need only retain the tags subvalue. It cannot answer arbitrary searches elsewhere in the document. On our four diagnostic arrays, this query and the whole-document gift containment query return the same labels; that equivalence depends on the tags-array contract. An object or scalar under tags can change their meanings.

A recurring capacity bound needs numeric order. The stricter teaware_documents contract makes this cast safe for every stored row:

CREATE INDEX teaware_capacity ON teaware_documents
  (((details ->> 'capacity_ml')::numeric));

SELECT product_id, (details ->> 'capacity_ml')::numeric AS ml
FROM teaware_documents
WHERE (details ->> 'capacity_ml')::numeric >= 300
ORDER BY (details ->> 'capacity_ml')::numeric;

The indexed expression is numeric extraction, rather than the document or its text spelling. The query repeats that expression for the bound and order. An unguarded cast over flexible documents can make index creation or a later write fail; the index is no substitute for deciding the input contract.

These definitions show eligible access paths, not a measured speed comparison. On a real catalogue, compare complete results first, then inspect EXPLAIN (ANALYZE, BUFFERS) under normal planner settings. Check the index condition, candidates reaching the heap, recheck rejects, separate filters, buffer work and any remaining sort. Include common as well as rare tags. Count index size and write work too: indexing every member maintains information that a tags-only query never asks for.

Changing part of a document

jsonb_set returns a changed JSONB value. In an UPDATE, assigning that result changes the row’s document. In an independent replay, change P7’s diagnostic capacity from 300 to 330 ml:

UPDATE product_documents
SET details = jsonb_set(details, '{capacity_ml}', '330'::jsonb)
WHERE product_id = 'P7'
RETURNING details;
-- {"capacity_ml": 330}

The default permits creation of the last path member, but earlier members must already exist. jsonb_set('{}', '{dimensions,capacity_ml}', '330') therefore leaves the empty object unchanged. JSONB assignment subscripting can create missing intermediate objects:

UPDATE product_documents
SET details['dimensions']['capacity_ml'] = '330'::jsonb
WHERE product_id = 'P7';

This is a separate path-creation example, not another authoritative capacity to add beside the first. Existing intermediate values must be traversable; a scalar or JSON null can make assignment fail. The value supplied is JSONB: '330'::jsonb is a number, while '"330"'::jsonb is a string.

Concatenation, ||, merges only the top level. Combining {"dimensions":{"capacity_ml":300}} with {"dimensions":{"width_mm":80}} replaces the entire dimensions object, losing capacity. It is not a recursive patch operation.

Suppose a client reads P7’s document with capacity 300. Another writer then adds care: "hand wash" and commits. If the first client replaces the whole document with its earlier copy modified to capacity 330, it erases the care instruction. A member update based on the current stored document changes the capacity while retaining that intervening addition.

Editing the current stored value avoids replacing unrelated members with an older client copy. It does not by itself protect a decision made from an earlier read. Two edits to the same capacity can still overwrite each other’s intended value. Use a transaction protocol or a version condition when the application must detect an intervening change; Transactions and isolation develops those choices.

Different members in one document still share a row lock. PostgreSQL does not give a capacity edit and a care edit independent key-level write locks. An update creates a new row version and can require index maintenance; naming a small path does not promise a few-byte physical write.

A whole-document GIN references the changed column, and so does an expression index over that column. That can rule out HOT even when an edit leaves the indexed expression’s result unchanged. Inside PostgreSQL follows those physical conditions. Split independently managed facts when their ownership and contention justify separate rows.

Keeping the schema useful as requirements change

A field can begin as optional catalogue description and become a required business fact. A capacity used for a range filter, shipping rule or external reference has acquired a stronger contract. Reassess its home then: the JSONB field, a typed column and a specialised row are alternatives with different validation and update costs.

Before promoting capacity, decide what missing and JSON-null values mean, which JSON types are accepted, how units are interpreted, and which existing rows need correction. Do not quietly accept numeric strings because a backfill can cast them. Once a typed column becomes authoritative, give readers and writers one clear contract rather than leaving two independently editable capacities.

That is a live migration, not merely an UPDATE that reaches the last row. Old callers, in-flight writes and failure partway through can reintroduce disagreement. Live schema changes and backfills and PostgreSQL schema changes under load own the handover and index-building procedures.

Keep JSON where its variable structure remains useful, and keep the schema visible where the application relies on stable facts. For P7, that means preserving product identity and independent memberships, making the capacity’s type and missing-value policy explicit, and choosing each query’s operator and index together. The document can change shape; the meaning still needs an owner.