Jsonpath And Jsonb

jsonpath-and-jsonb — SQL/JSON path language and binary JSON

PG has TWO JSON types:

• json — text storage. Preserves formatting/whitespace/key-order. Slow to query (parsed every access).
• jsonb — binary storage. Deduped/sorted keys, faster to query, indexable via GIN. Almost always what you want.

Plus jsonpath — a query language type (SQL:2016). Compiled path expressions used by jsonb_path_query, jsonb_path_exists, @@ operator, JSON_TABLE.

The file map

jsonb storage layout

A jsonb is a varlena with:

• Header — 4 bytes VARSIZE_ANY + 4 bytes JEntry-array-count for the root container.
• JEntry array — one per top-level element, encoding type + length/offset.
• Serialized values — the raw bytes for scalars, or nested containers with their own JEntry arrays.
• Recursive — a jsonb OBJECT's value is a nested container repeating the pattern.

Keys are sorted lexically (bytewise UTF-8) and deduped (last-wins semantics). This is why:

• '{"a": 1, "a": 2}'::jsonb returns {"a": 2}.
• -> on a large object is O(log n) via binary search on sorted keys.

jsonpath language basics

Path expressions like $.employees[*] ? (@.name == "Alice") mean:

• $ — the root (the jsonb value passed in).
• .name — field access.
• [*] — all array elements.
• ? (predicate) — filter — keep only elements where predicate is true.
• @ — the current element in a filter context.

Two modes:

• lax (default) — missing paths return "no result" instead of erroring. Auto-unwraps arrays. Auto-wraps scalars. Forgiving.
• strict — errors on missing paths / type mismatches. Strict RFC compliance.

Set at query time: SELECT jsonb_path_query(v, 'strict $.a').

The executor's key structures

From jsonpath_exec.c:

• JsonPathExecContext — walks state: current position, mode, variables map.
• JsonValueList — accumulates result values. This is the struct Tom Lane's 5a2043bf713 redesigned inline to eliminate the executePredicate memory leak. See planning/jsonpath_leak/comparison.md for the pattern comparison.
• JsonPathItem — one node in the compiled path tree.
• JsonbValue — the value being examined.

The executor does DFS over the jsonpath tree, executing predicates, applying arithmetic, accumulating results into a JsonValueList.

GIN index on jsonb

Two operator classes:

• jsonb_ops (default) — index each unique key/value combo. Supports @>, ?, ?&, ?|. Bigger index; more query flexibility.
• jsonb_path_ops — hash each path-to-value. Only supports @>. Smaller; faster; strict subset.

Choose path_ops for pure containment queries (JSON-Blob search). Choose default for key-existence + containment.

Common patch shapes

Add a new jsonpath function

• Extend jsonpath_gram.y for the syntax.
• Add case in executeItem (jsonpath_exec.c) — implement the semantics.
• If the function returns multiple values, decide lax vs strict behavior.
• Regress in src/test/regress/sql/jsonpath.sql.

Add a new jsonb operator

• Add C function in jsonb.c or jsonb_op.c.
• pg_operator.dat entry (with commutator / negator / restrict / join fns).
• Consider GIN opclass support — a new operator without index support is a footgun.

Optimize a jsonb query

• EXPLAIN — is the GIN index being used?
• If not: path_ops may not support your operator; switch to default. Or your predicate has too much post-index work.
• If yes but slow: consider a partial index on the specific path shape.
• Container-emit vs path-emit — the executor may be materializing large intermediate results. Consider strict mode + explicit path.

Fix a jsonpath memory leak

Been done. See planning/jsonpath_leak/:

• The blind-trilogy calibration reproduced Tom Lane's leak fix (5a2043bf713) with a different mechanism but identical RSS envelope (32 MB vs 32 MB).
• The bug pattern: JsonValueList accumulated results without per-call context cleanup. Fixes: struct redesign (Tom's) OR per-call AllocSetContext wrap (calibration's approach). Both work.
• Lesson: for iterator-style accumulator structs, ownership of the elements matters as much as ownership of the containers.

Pitfalls

• json vs jsonb type — pick jsonb — unless you have a specific reason (preserving formatting, key order), always jsonb. json is a "text with syntax check", not a queryable structure.
• Key deduplication in jsonb — '{"a":1,"a":2}'::jsonb silently keeps the last. Applications that generate jsonb should not rely on duplicate keys.
• jsonpath variables need explicit passing — jsonb_path_query(v, '$var', '{"var": 5}'::jsonb). Beginners often try to embed variables inline.
• -> returns jsonb; ->> returns text — using -> when you want a text scalar leaves you with a jsonb-encoded scalar (e.g. "hello" with quotes).
• @? vs @@ — @? returns bool (path exists); @@ returns bool (path returns truthy). Common mixup.
• jsonb_path_ops is more restrictive than you think — no ? operator, no -> (that's the wrong tool anyway).
• Deep jsonpath is quadratic — a path like $.a.b.c...z on a deeply nested jsonb is O(depth × object-size). Consider caching sub-values.
• jsonpath_exec.c is a memory hotspot — see planning/jsonpath_leak/ for a real bug fixed at scale (5.7 GB → 32 MB RSS on the reproducer). New iterator-style code paths should apply the per-call context pattern.
• JSON_TABLE (PG 17+) is jsonpath at heart — its columns are jsonpath expressions. Same performance considerations.
• JIT doesn't help jsonb operations — jsonpath executor is interpreter-only. JIT compiles ExprState machinery, not the jsonpath tree.

Related corpus

• Idioms: error-handling (jsonpath errors in strict mode), heap-tuple-decompression-pattern (jsonb is toasted → detoast + traverse).
• Subsystems: utils-mmgr (jsonpath_exec is a memory hotspot — hence the leak), parser-and-rewrite (jsonpath grammar is separate but sits in the same layer).
• Scenarios: add-new-builtin-function (for adding jsonb / jsonpath fns), fix-memory-leak (jsonpath_leak is the canonical calibration).
• Planning: planning/jsonpath_leak/ — 3-phase blind calibration reproducing Tom Lane's fix. Read comparison.md for the mechanism comparison (inline storage vs per-call context).

Corpus-chain shortcut

python3 scripts/corpus-chain.py --file src/backend/utils/adt/jsonpath_exec.c
python3 scripts/corpus-chain.py --file src/backend/utils/adt/jsonb.c
python3 scripts/corpus-chain.py --idiom heap-tuple-decompression-pattern

Boundary

Use this skill for jsonb + jsonpath + related operators + JSON_TABLE.

Don't use for:

• json type (text-based) — separate json.c; use only for compatibility.
• hstore — contrib module, different data model.
• hstore_plpython / hstore_plperl — different transformers.
• XML type — xml.c etc., separate subsystem.
• JSON parsing at the SQL boundary — that's jsonapi.c's pull-based parser, used by many callers; often you don't need to touch it.