DuckDB JSONO

A DuckDB extension for analytics-optimized JSON storage and querying.

The extension has three main goals:

• spec-driven multi-field extraction from a JSON document into a typed STRUCT with jsono_transform;
• a pre-parsed JSONO representation for repeated reads over the same JSON column;
• path-shredded JSONO storage (jsono(value, shredding := spec)) that lifts hot paths into typed shred columns for fast columnar reads while keeping ->> and to_json working transparently.

Warning

This extension is experimental and maintained on a best-effort basis by a developer who doesn't write C++ professionally, so expect rough edges. It has not been hardened for production, and the JSONO binary format and SQL API are still being designed and may change between revisions. Validate behavior and performance on your own data before relying on it. Feedback and contributions are welcome via GitHub.

Functions

Build and convert:

• jsono(json) / try_jsono(json) — parse JSON text or JSON into JSONO (try_ returns NULL on malformed input instead of raising).
• jsono(struct) — construct JSONO directly from typed DuckDB values, without serializing through JSON text first.
• to_json(value) — serialize JSONO back to compact JSON (also available as a CAST).

Extract and project:

• jsono_extract(value, path) / value -> path — extract one value as JSONO.
• jsono_extract_string(value, path) / value ->> path — extract one value as VARCHAR.
• jsono_transform(value, spec[, on_type_mismatch]) — project many fields into a typed STRUCT in one pass (spec is a STRUCT literal; type mismatches default to convert).
• jsono_entries(value[, key_style, array_style]) — flatten scalar leaves into STRUCT(key VARCHAR, value VARCHAR)[].
• jsono_array_elements(value[, path]) — a JSON array's elements as a LIST of self-contained JSONO values, ready to unnest and query natively.

Merge and aggregate:

• jsono_merge_patch(target, patch[, ...]) — RFC 7396 merge patch (later patches win; a null member deletes the key).
• jsono_group_merge(value [ORDER BY ...]) — aggregate a stream of patches into one value.
• jsono_group_merge_max(value, order_key) / jsono_group_merge_min(value, order_key) — order-independent keyed merge: per leaf, the value from the row with the greatest (_max) or smallest (_min) order_key wins, without buffering the input.
• jsono_diff(prev, cur[, arrays]) — structural diff of prev → cur; arrays selects how array changes render (atomic reverse merge-patch, counts, or elements).
• jsono_group_array(value [ORDER BY ...]) — aggregate a group's values into one JSONO array, in fold order.
• jsono_group_object(key, value) — aggregate key → value pairs into one JSONO object (unique sorted keys, last value per key wins).

Shredded storage:

• jsono(value, shredding := spec) — build a shredded STRUCT from JSON text or an existing JSONO, lifting hot paths into typed shred columns; ->> and to_json stay transparent and read the shreds.
• jsono_suggest_shredding(value[, min_presence, min_fit]) — aggregate a plain JSONO column into a ready-to-paste shredding := spec string.
• jsono_shred_stats(value) — aggregate an existing shredded column into per-shred lane / divert / complete rates.

Inspect:

• jsono_type(value[, path]) — the value's JSON type as VARCHAR (OBJECT, ARRAY, a scalar type, or NULL).
• jsono_keys(value[, path]) — object keys as VARCHAR[].
• jsono_array_length(value[, path]) — element count of the array as BIGINT, or NULL for a non-array.
• jsono_validate(value) — strict current-format validation as BOOLEAN.
• jsono_storage_size(value) — physical byte sizes (body blobs, shreds, total) as a STRUCT.
• jsono_storage_type() — DDL of the physical STRUCT backing a JSONO value.
• jsono_shred_manifest(value) — paths stripped into shred columns and their types as a LIST<STRUCT(path, type)>.
• jsono_version() — the current JSONO binary format version as INTEGER.

Quick Start

Note

JSONO is a format, not a SQL type. A JSONO value is this extension's pre-parsed binary representation of a JSON document — physically a nested STRUCT(jsono STRUCT(body STRUCT(slots BLOB, key_heap BLOB, string_heap BLOB, skips BLOB, lengths BLOB, nums BLOB))), with no registered type name. There is no JSONO to name in a CAST or a column definition. Instead: build a value with jsono(...) / try_jsono(...), declare storage columns with jsono_storage_type(), and let the functions, the -> / ->> operators, and to_json recognise that STRUCT shape structurally — a value read back from Parquet or DuckLake works with no cast. .body (and, on a shredded value, the shred fields in its sibling shreds struct) are physical storage details, not a public access API.

Build and load the extension first (see Installation), then:

SELECT jsono_transform(
    jsono('{"id": 42, "name": "duck", "active": true}'),
    {id: 'BIGINT', name: 'VARCHAR', active: 'BOOLEAN'}
);
-- {'id': 42, 'name': duck, 'active': true}

Convert to JSONO when the same JSON values will be read repeatedly:

CREATE TABLE events AS
SELECT jsono(payload) AS payload_jsono
FROM read_parquet('events.parquet');

SELECT (jsono_transform(payload_jsono, {user_id: 'VARCHAR', event_ts: 'BIGINT'})).*
FROM events;

Installation

Note: This extension is not yet published in DuckDB's extension repository, and the JSONO binary format and SQL API are still being designed. Build it from source and load the produced local extension.

Prerequisites

• uv — drives the build and the Python tooling.
• A C++17 toolchain and CMake.
• Ninja — the build defaults to the Ninja generator; pass GEN=make to fall back to Make.
• ccache (optional) — speeds up incremental rebuilds.

CLI

uv run make release
./build/release/duckdb -unsigned

LOAD './build/release/extension/jsono/jsono.duckdb_extension';

Python

import duckdb

con = duckdb.connect(":memory:", config={"allow_unsigned_extensions": "true"})
con.execute("LOAD './build/release/extension/jsono/jsono.duckdb_extension'")

Usage

jsono(json)

Strictly parses JSON text or JSON into JSONO.

SELECT jsono('{"a":1,"b":"x"}');

Malformed or empty input raises an error. Use try_jsono when malformed input should become NULL:

SELECT try_jsono('{not json') IS NULL;

To parse and shred hot paths into typed shred columns in one pass, supply a constant shredding := {...} spec — the same option documented under the shredding constructor below:

SELECT jsono('{"kind":"commit","time_us":1700}', shredding := {'$.kind': 'VARCHAR', '$.time_us': 'BIGINT'});

A JSONO value is physically this nested STRUCT — one jsono layout field wrapping the six body BLOBs every storage layer (DuckDB-native, Parquet, DuckLake) sees:

STRUCT(jsono STRUCT(body STRUCT(slots BLOB, key_heap BLOB, string_heap BLOB, skips BLOB, lengths BLOB, nums BLOB)))

The JSONO functions (to_json, jsono_transform, …) recognise this STRUCT structurally, so a value read back from Parquet or DuckLake binds to them with no cast. jsono_storage_type() returns the DDL string above for declaring storage columns.

The binary layout of each body BLOB (slot encoding, tag table, string/key heaps, the skips navigation metadata, and the lengths/nums side columns) is specified in jsono_format.md.

jsono(struct)

Constructs JSONO directly from typed DuckDB values without serializing the whole input through JSON text first.

SELECT to_json(jsono({'b': 2, 'a': 'x', 'n': NULL, 'arr': ['c', NULL, 'd']}));

Result:

{"a":"x","arr":["c",null,"d"],"b":2,"n":null}

STRUCT fields become JSON object keys, LIST values become arrays, JSON children are parsed as JSON subtrees, and VARCHAR children remain JSON strings. Exact integer and decimal values are preserved; non-finite DOUBLE values are rejected because JSON has no NaN/Infinity representation. SQL NULL object fields are serialized as JSON nulls.

Input types and how they render:

Input type	JSON
BOOLEAN	true / false
TINYINT … BIGINT, unsigned narrow ints, HUGEINT/UHUGEINT/UBIGINT, VARINT, DECIMAL	number, exact digits
FLOAT, DOUBLE	number (non-finite values are rejected)
VARCHAR	string
JSON	parsed subtree
DATE, TIME(_NS), TIMETZ, TIMESTAMP(_S/_MS/_NS/TZ), UUID, ENUM, INTERVAL, BLOB, BIT	string, rendered exactly as core to_json renders it
STRUCT, MAP	object
LIST, ARRAY	array
a jsono value	composed as a subtree, no text round-trip (a shredded value is reconstructed first)

A MAP builds an object whose keys are per-row data: keys render as text (as in core to_json) and are sorted, which is where JSONO diverges from core's insertion order — the pairs are the same. Two distinct typed keys that render to the same text would collapse two entries into one, so that case raises an error instead. UNION is not supported and raises a bind error.

TIMESTAMPTZ renders in UTC (2024-01-01 12:00:00+00), not in the session TimeZone: a document is storage, so the same instant must produce the same bytes — and the same shred lane, whose Parquet min/max drives row-group pruning — in every session. This matches what core to_json writes.

The document root need not be an object: jsono(MAP {...}), jsono([1, 2, 3]) and jsono(array_value(1, 2)) build object/array documents, and the matching casts let you INSERT a MAP, a LIST or an ARRAY straight into a JSONO column. A VARCHAR root is the text parser by contract, so scalar roots are not constructible — wrap them in a list or an object.

Because the input STRUCT already carries its field names and types, jsono(struct) shreds automatically — there is no shredding argument to pass. Every top-level field whose type is a scalar shred type (BIGINT, UBIGINT, DOUBLE, BOOLEAN, or VARCHAR) is lifted into a typed shred column named by the field, exactly as the shredding constructor does for text. Narrow numeric fields are promoted to their shred type and lifted as well: TINYINT/SMALLINT/INTEGER and the unsigned UTINYINT/USMALLINT/UINTEGER become BIGINT shreds, and FLOAT becomes a DOUBLE shred, so to_json output is unchanged. The string-rendered types (TIMESTAMP, DATE, UUID, ENUM, BLOB, … — the string row of the table above) become VARCHAR shreds carrying exactly the text the document holds. Top-level LIST<TYPE> (and ARRAY<TYPE>) fields also become scalar-array shreds when TYPE is (or promotes to) one of those scalar shred types, and top-level LIST<STRUCT<...>> fields become array shreds when the struct children are (or promote to) supported scalar shred types. Nested STRUCT levels are eligible, with shred leaves named by JSONPath-style paths such as $.parent.child and $.URL.query_params.utm_source. Auto-shred descends nested structs up to a fixed depth cap set deliberately high (far above realistic analytical nesting); the cap exists only to keep a pathologically deep value's whole-value reconstruct from going super-linear, not to limit real use — a scalar leaf strictly past it, unsupported lists, HUGEINT/UHUGEINT/VARINT, DECIMAL, MAP, NULL, and JSON subtrees stay in the residual (and a deeper leaf can still be lifted explicitly with shredding := {...}). Shredding lifts named top-level object fields, which only a STRUCT root has, so MAP, LIST, ARRAY and jsono roots construct plain JSONO. An embedded jsono field is a document, not a struct to walk, so it stays in the residual whole.

SELECT typeof(jsono({'kind': 'commit', 'time_us': 1700::BIGINT}));
-- a shredded STRUCT: a `jsono` layout wrapping the body blobs plus shreds "kind" VARCHAR and "time_us" BIGINT

to_json

Serializes JSONO back to compact JSON.

SELECT to_json(jsono('{"b":2,"a":1}'));

Result:

{"a":1,"b":2}

Object keys are emitted in sorted byte order. Explicit casts are also available:

SELECT CAST(jsono('{"a":1}') AS JSON);
SELECT CAST(jsono('{"a":1}') AS VARCHAR);

The JSON cast and to_json produce DuckDB's core JSON logical type, which is provided by the bundled json extension that ships with every standard DuckDB distribution — no manual setup is needed.

jsono_extract

Extracts one value using a constant path. value -> path is an alias for the same operation. A bare string path names a literal top-level key, a string path starting with $ uses JSONPath, and a BIGINT path indexes an array:

SELECT CAST(jsono_extract(jsono('{"a.b":"dot","a":{"b":"nested"}}'), 'a.b') AS VARCHAR);
-- "dot"

SELECT CAST(jsono('{"a.b":"dot","a":{"b":"nested"}}') -> '$.a' AS VARCHAR);
-- {"b":"nested"}

SELECT jsono('{"items":[{"name":"duck"}]}') -> 'items' -> 0 ->> 'name';
-- duck

JSON null at the selected path returns a JSONO null value. Missing paths return SQL NULL.

jsono_extract_string

Extracts one value using a constant path. value ->> path is an alias for the same operation. Path rules match jsono_extract:

SELECT jsono_extract_string(jsono('{"a.b":"dot","a":{"b":"nested"}}'), 'a.b');
-- dot

SELECT jsono('{"a.b":"dot","a":{"b":"nested"}}') ->> 'a.b';
-- dot

SELECT jsono_extract_string(jsono('{"a.b":"dot","a":{"b":"nested"}}'), '$.a.b');
-- nested

JSON strings return unquoted text, numbers and booleans return their JSON text, JSON null and missing paths return SQL NULL, and arrays/objects return compact JSON text.

Operator precedence. As in core json, the -> and ->> operators bind looser than AND/OR — the arrow token is shared with lambda syntax, whose body must bind loosely. Inside a boolean conjunction you must therefore parenthesize the extraction: write WHERE cond AND (e ->> '$.k') = 'v', not WHERE cond AND e ->> '$.k' = 'v' — the latter parses as (cond AND e) ->> … and fails trying to cast the JSONO struct to BOOLEAN. The named jsono_extract / jsono_extract_string functions need no parentheses. This is expected DuckDB behavior (duckdb/duckdb#16970).

jsono_transform

Extracts fields from JSONO into a typed DuckDB STRUCT. spec must be a constant STRUCT mapping each output field name to an extraction rule.

SELECT jsono_transform(
    jsono('{"a":42,"b":3.5,"c":"hi","d":true}'),
    {a: 'BIGINT', b: 'DOUBLE', c: 'VARCHAR', d: 'BOOLEAN'}
);

A rule is either a type shorthand string or a wrapper STRUCT. Supported scalar types:

• BIGINT
• UBIGINT
• DOUBLE
• VARCHAR
• BOOLEAN

A shorthand reads the matching top-level key (path defaults to $."field"). A wrapper STRUCT reads from an explicit JSONPath:

SELECT jsono_transform(
    jsono('{"event":{"timestamp":1234567890}}'),
    {ts: {type: 'BIGINT', path: '$.event.timestamp'}}
);

Paths support nested keys, array indices, quoted keys, and the [*] wildcard: $.user.name, $.items[0].id, $."my-key", $.tags[*].

Type mismatch handling is controlled by the constant on_type_mismatch argument:

• convert (default): CAST-like scalar coercion to the requested type. Values that cannot be coerced become NULL.
• null: keep the legacy silent-NULL behavior. A scalar value is returned only when its stored JSON type already matches the requested projection type.
• fail: use the same scalar coercions as convert, but raise an error when a present value cannot be coerced.

Missing paths and explicit JSON null return SQL NULL in every mode. on_type_mismatch applies independently to each projected field and each wildcard list element.

SELECT jsono_transform(jsono({'a':'12345'}), {'a':'UBIGINT'});
-- {'a': 12345}

SELECT jsono_transform(jsono({'a':'12345'}), {'a':'UBIGINT'}, on_type_mismatch := 'null');
-- {'a': NULL}

SELECT jsono_transform(jsono({'a':'abc'}), {'a':'UBIGINT'}, on_type_mismatch := 'fail');
-- error: cannot convert value at $.a from VARCHAR to UBIGINT

Collect array elements into a typed list with a single-element list type, or join them into one string with join_separator:

SELECT jsono_transform(
    jsono('{"values":["1",2,true,"bad"]}'),
    {values: {type: ['UBIGINT'], path: '$.values[*]'}}
);
-- {'values': [1, 2, 1, NULL]}

SELECT jsono_transform(
    jsono('{"tags":["a","b","c"]}'),
    {tags: {type: 'VARCHAR', path: '$.tags[*]', join_separator: ','}}
);

The field names type, path, and join_separator are reserved inside a wrapper STRUCT.

jsono(value, shredding)

The shredding named argument turns jsono() into a shredding constructor: it produces a shredded STRUCT — the six-BLOB JSONO residual plus a shreds struct carrying one typed shred per path in spec. The primary form takes JSON text and parses + shreds in one pass; passing an existing JSONO value shreds it directly. Those are the two accepted value inputs — there is no jsono(struct, shredding := …) overload, because a STRUCT built with jsono(struct) is already shredded by that constructor (so to shred a struct-built value, build it with jsono({…}) directly rather than wrapping it). Reads stay transparent — ->>, jsono_extract_string, to_json, and casts work on the shredded value exactly as on a plain JSONO column — but extracting a shredded path becomes a direct read of its typed shred instead of a per-row parse, which the planner can push down and prune on.

spec is a constant STRUCT mapping each path to its shred type string (the same shape as Parquet's SHREDDING option and read_json's columns; same path grammar as jsono_transform, no wildcards). Scalar shreds use VARCHAR, BIGINT, UBIGINT, DOUBLE, or BOOLEAN; scalar-array shreds use VARCHAR[], BIGINT[], UBIGINT[], DOUBLE[], or BOOLEAN[]; object-array shreds use STRUCT(<supported scalar children>)[]:

CREATE TABLE events AS
SELECT jsono(payload, shredding := {'$.kind': 'VARCHAR', '$.did': 'VARCHAR', '$.time_us': 'BIGINT'}) AS payload
FROM read_parquet('events.parquet');

-- queried like any JSONO column; the planner reads the shreds
SELECT payload ->> '$.kind' AS kind, count(*) AS n
FROM events GROUP BY kind;

SELECT max(CAST(payload ->> '$.time_us' AS BIGINT)) FROM events;

Array-shred specs name the array's object-key path, not an indexed or wildcard element path. Elements or subfields that do not fit the requested shred type stay in the residual skeleton, so reconstruction never loses or coerces data.

SELECT to_json(jsono('{"products":[{"id":1,"name":"a"}]}',
                     shredding := {'$.products': 'STRUCT(id UBIGINT, name VARCHAR)[]'}));
-- {"products":[{"id":1,"name":"a"}]}

The conversion is lossless: to_json(payload) reconstructs the original document, and any path not in spec still reads from the residual. A value that does not fit its shred type (a string in a BIGINT shred, an explicit JSON null, a missing key) is kept in the residual unchanged, so reconstruction never loses or coerces data. Top-level shredded paths are stored once — in the shred rather than duplicated in the residual — so the shredded column is typically smaller than the plain JSONO column for the same data while answering hot-path queries from narrow typed columns.

SELECT to_json(jsono('{"kind":"commit","time_us":1700,"extra":"e1"}',
                     shredding := {'$.kind': 'VARCHAR', '$.time_us': 'BIGINT'}));
-- {"extra":"e1","kind":"commit","time_us":1700}

Transparent reads over a shredded value go through the bundled json extension (present in every standard DuckDB distribution) and the extension's query optimizer. The shredded shape — the jsono layout wrapping the body blobs and the named shreds columns — survives a plain Parquet round-trip and reads back transparently; the scalar shred leaves keep projection and filter pushdown.

Two things decide whether a filter actually prunes Parquet row groups (skips them on the per-row-group min/max statistics) rather than scanning the whole file:

• Filter a typed shred in its own type. A VARCHAR shred prunes on the plain payload ->> '$.kind' = 'commit'. A numeric/boolean shred prunes only when the comparison is in the shred's type — CAST(payload ->> '$.time_us' AS BIGINT) = 1700, not the string form payload ->> '$.time_us' = '1700'. ->> is textual, so the string form compares the shred rendered back to text (a computed expression the scan cannot push down) and reads every row group. Compare with CAST(... AS <shred type>) (or project the typed value with jsono_transform) to get pruning on a typed shred.
• Cluster the rows on the leaf at write time. Pruning needs each row group to hold a disjoint range of the filtered path, so add ORDER BY on that path to the CREATE TABLE … AS SELECT (or the COPY). Without clustering every row group spans the whole domain and none can be skipped. Clustering also improves compression, so one ORDER BY on the dominant filter path pays off twice.

CREATE TABLE events AS
SELECT jsono(payload, shredding := {'$.kind': 'VARCHAR', '$.time_us': 'BIGINT'}) AS payload
FROM read_parquet('events.parquet')
ORDER BY CAST(payload ->> '$.time_us' AS BIGINT);   -- cluster on the hot filter path

-- prunes to the matching row groups (typed shred compared as BIGINT)
SELECT count(*) FROM events WHERE CAST(payload ->> '$.time_us' AS BIGINT) BETWEEN 1700 AND 1800;

Reading across many Parquet files, pass union_by_name := true. A plain multi-file scan (read_parquet('dir/*.parquet'), read_parquet([...]), or a DuckLake table over many data files) returns no per-file column statistics to the optimizer, so a shred read there falls back to a COALESCE(shred, residual) that reads the lane but can no longer prune row groups. Under union_by_name := true the engine surfaces merged (and, for files with different shred sets, recovered) statistics, the shred proves total, and the read lowers to a bare, prunable struct_extract_at — the same plan as a single-file read. Make it the default for multi-file shredded facts:

-- prunes across files; without union_by_name this keeps a non-prunable COALESCE
SELECT count(*) FROM read_parquet('events/*.parquet', union_by_name := true)
WHERE CAST(payload ->> '$.time_us' AS BIGINT) BETWEEN 1700 AND 1800;

Values shredded differently compose freely. A set operation, CASE or COALESCE over differently-shredded values merges into one shredded type on the union of the shred sets, and an INSERT into a column with any other shred set — fully disjoint included — lands losslessly: the optimizer rewrites the struct cast into a reshred against the column's shred set (single-pass when the target keeps every source shred), and a path with no shred column in the target simply stays in the residual.

Most shredded reads never rebuild the whole document — ->>, typed-shred filters, and a jsono_transform field on a scalar shred leaf read the shred column directly. A read that needs the whole logical value instead (a jsono_transform field descending into an array shred is the common case) reconstructs the shredded value to plain JSONO first, a 3-10x per-row cost. A jsono_transform field forcing that reconstruct surfaces it as an explicit __jsono_reconstruct operator in EXPLAIN / EXPLAIN ANALYZE; the other whole-value reads (jsono_array_elements, the collect aggregates) show it as an anonymous CAST(… AS STRUCT(jsono …)), and jsono_diff shows that CAST only when its lane-aware fast path declines (array shreds, or the two sides typed differently). If you see either, prefer the shred-aware paths — filter or jsono_transform on the scalar shred leaves, or shred the exact path you read — so the query reads narrow typed columns instead of rebuilding the document.

The safety net for the raw struct cast (no extension optimizer: another process, SET disabled_optimizers='extension') is the shred manifest: each shredded value records, inside its residual, which paths were stripped into shred columns and as what type. A cast that silently drops or retypes a shred column leaves that record contradicting the data, and every JSONO reader verifies it — reading such a narrowed row raises an error instead of silently returning partial data. The manifest lives in the value's own blobs, so the protection rides with the data through Parquet and DuckLake. Shred order does not matter: the shred field order is canonicalized (sorted by name), so the same shred set always yields the identical type — jsono_storage_type(<shred DDL>) and CREATE TABLE … AS SELECT produce the same column type regardless of the order shreds are written in.

Is my read pushed down?

Whether a read hits a shred lane (fast, and row-group-prunable) or rebuilds the document is visible in the plan: run EXPLAIN <your query> (or EXPLAIN ANALYZE for timings) and read the physical_plan. If the only jsono touch over a shredded column is a read of its .shreds."<path>" column, the read is pushed down; if any of the residual/reconstruct tokens below appear, it is not.

• Pushed — reads a typed shred column, prunable on its min/max statistics:
  • a bare .shreds."<path>" — a total shred, COALESCE-free. A projection shows it as a scan projection column (ep.jsono.shreds."<path>"); a filter or aggregate shows it as a struct_extract_at(...shreds."<path>"...).
  • COALESCE(struct_extract_at(...shreds...), jsono_extract_string(...)) — partial: the lane is still read (and prunable), with a per-row residual fallback kept for diverted rows. jsono_shred_stats' divert_rate shows how often that fallback fires.
• Residual — parses the six body blobs per row, not prunable:
  • jsono_extract_string / jsono_extract — a single extract on a path that has no shred.
  • __jsono_internal_project — two or more residual extracts on the same column fused into one document walk.
• Reconstruct — rebuilds the whole document (a 3–10× per-row cost):
  • __jsono_reconstruct — explicit (a jsono_transform field descending into an array shred).
  • jsono_overlay — a whole-value read as JSON (to_json, ::JSON).
  • an anonymous CAST(… AS STRUCT("jsono" …)) — jsono_array_elements, the collect aggregates, and jsono_diff when its lane-aware fast path declines.

If a read you expect to be hot shows a residual/reconstruct token, shred the exact path (the plan says whether it can push down; jsono_shred_stats says how well an existing lane actually serves it), then apply the pruning rules above (filter the typed shred in its own type; ORDER BY the hot leaf at write time). For reads across many Parquet files, pass union_by_name := true (see below) so the lane stays a bare, prunable struct_extract_at instead of a COALESCE.

jsono_suggest_shredding

Aggregates a plain JSONO column and returns a ready-to-paste shredding := spec string, so you don't have to hand-pick shred paths and types. It walks each document over what the constructor auto-shreds — top-level scalar keys, nested scalar keys ($.parent.child, $.a.b.c — up to the same high auto-shred depth cap, so web-event leaves like $.URL.query_params.utm_source are suggested), and top-level arrays (as scalar-array TYPE[] or object-array STRUCT(...)[]) — and picks, per path, the narrowest lane type every present value fits losslessly. Nested arrays ($.parent.items) are outside that set even though the constructor lifts them, so a nested list path never appears in the suggested spec.

SELECT jsono_suggest_shredding(payload) FROM events;
-- {"$.meta.seq": 'BIGINT', kind: 'VARCHAR', tags: 'BIGINT[]'}

-- paste the result straight into the shredding constructor
CREATE TABLE shredded AS
SELECT jsono(payload, shredding := {"$.meta.seq": 'BIGINT', kind: 'VARCHAR', tags: 'BIGINT[]'}) AS payload
FROM events;

Two named arguments tune the thresholds (both fractions in [0.0, 1.0]):

• min_presence (default 0.5) — keep a path only when it appears in at least this fraction of non-NULL rows. Sparse fields drop out.
• min_fit (default 1.0, fully lossless) — a lane type must fit at least this fraction of the rows that carry the path. Every row where the key is present counts toward the denominator, including rows whose value is an object (or a nested container) that no lane can lift — so a key that is a scalar in some rows and an object in others drops at min_fit := 1.0. Lowering it accepts a majority type for a path with a few off-type values (which stay in the residual). An explicit JSON null is lossless in every lane (it stays complete in the residual), so it counts as fitting any type and never blocks a shred at min_fit := 1.0.

SELECT jsono_suggest_shredding(payload, min_presence := 0.9, min_fit := 0.99) FROM events;

Number lanes follow the BIGINT > DOUBLE > BOOLEAN > VARCHAR preference, with UBIGINT chosen only when a value exceeds the int64 range. A path with no qualifying lane is left out; if nothing qualifies (or the column is all NULL), the result is NULL. The input must be the plain column — suggesting from an already-shredded column is jsono_shred_stats's job.

jsono_shred_stats

Aggregates an existing shredded column and reports, per shred, how well the shred set is working — as a LIST<STRUCT(path VARCHAR, type VARCHAR, lane_rate DOUBLE, divert_rate DOUBLE, complete_rate DOUBLE)> sorted by path:

• lane_rate — fraction of non-NULL rows whose typed lane is populated (by a lifted value).
• divert_rate — fraction of non-NULL rows where the lane is NULL because the value did not fit its shred type and stayed behind in the residual. A high divert rate means the shred type is too narrow. An explicit JSON null (a complete NULL lane) is lossless and does not count as a divert.
• complete_rate — fraction of rows where a bare typed lane read is the full ->> answer (a fit, an absent path, or an explicit null). Array shreds report 1.0.

SELECT to_json(jsono_shred_stats(payload)) FROM shredded;
-- [{"path":"kind","type":"VARCHAR","lane_rate":1.0,"divert_rate":0.0,"complete_rate":1.0},
--  {"path":"time_us","type":"BIGINT","lane_rate":0.98,"divert_rate":0.02,"complete_rate":0.98}]

Plain (non-shredded) input is rejected — use jsono_suggest_shredding to propose shreds for a plain column.

jsono_entries

Flattens scalar leaves of a JSONO document into a list of key/value structs. The default key_style is 'jsonpath'; pass the named argument key_style := 'dotted' for dot-separated keys:

SELECT unnest(jsono_entries(jsono('{"a":1,"b":{"c":"x"},"d":true}')));
-- {'key': $.a, 'value': 1}
-- {'key': $.b.c, 'value': x}
-- {'key': $.d, 'value': true}

SELECT unnest(jsono_entries(jsono('{"a":1,"b":{"c":"x"}}'), key_style := 'dotted'));
-- {'key': a, 'value': 1}
-- {'key': b.c, 'value': x}

The named argument array_style selects where the walk stops at an array. The default 'indexed_elements' recurses into arrays, giving each element an indexed key (arr[0], arr.0, …). Pass array_style := 'whole_json' to stop at the array boundary and emit each array as a single leaf whose value is the whole array as JSON text:

SELECT unnest(jsono_entries(jsono('{"items":[{"sku":"a"},{"sku":"b"}],"name":"x"}'), array_style := 'whole_json'));
-- {'key': $.items, 'value': [{"sku":"a"},{"sku":"b"}]}
-- {'key': $.name,  'value': x}

array_style is orthogonal to key_style and both are order-independent. In 'whole_json' an array leaf's value is a JSON literal (parse it as JSON), while a scalar leaf stays bare text; an empty array surfaces as a present [] value; and a document whose top level is itself an array is rejected at runtime (the mode is defined for object documents whose array-valued fields render whole).

JSON null leaves keep their key and return SQL NULL as value; empty objects (and, under indexed_elements, empty arrays) do not produce entries. Dotted output can collide when a literal dotted key and a nested path render to the same string, for example "a.b" and {"a":{"b":...}}.

The order of the returned entries is unspecified — do not rely on it. Over a shredded value the shred leaves are emitted after the residual leaves (the shreds are read straight from their columns), so the same logical document can flatten in a different order than its plain form, which lists leaves in sorted-key order. Sort the result yourself if you need a stable order.

jsono_array_elements

Returns a JSON array's elements as a LIST of self-contained JSONO values — each element becomes a complete JSONO document of its own, so unnest-ing the list gives one native JSONO value per row with no to_json + reparse round-trip:

SELECT unnest(jsono_array_elements(jsono('[{"name":"a"},{"name":"b"}]'))) ->> 'name';
-- a
-- b

The optional path addresses a nested array with the same grammar as jsono_extract — a constant JSONPath ('$.items'), a bare top-level key ('items'), or a non-negative array index:

SELECT to_json(jsono_array_elements(jsono('{"items":[7,8,9]}'), '$.items')[2]);
-- 8

An empty array yields an empty LIST ([]). A non-array value (object, scalar, or JSON null), a missing path, or a SQL NULL input each yield SQL NULL. A JSON null element becomes a JSONO null document, kept in place. A shredded value is reconstructed through the validated read path before its elements are split out.

jsono_group_merge

Aggregates a stream of JSON object patches with RFC 7396 merge semantics: later patches in the ordered stream overwrite earlier keys and arrays replace wholesale. null object members are dropped before merging so existing keys stay untouched. Provide an ORDER BY clause to make the fold deterministic.

WITH patches(patch, ts) AS (
    VALUES
        (jsono('{"a":1,"b":{"x":1}}'), 1),
        (jsono('{"a":null,"b":{"y":2}}'), 2),
        (jsono('{"c":3}'), 3)
)
SELECT to_json(jsono_group_merge(patch ORDER BY ts))
FROM patches;

Result:

{"a":1,"b":{"x":1,"y":2},"c":3}

Use GROUP BY for grouped state accumulation:

SELECT session_id,
       to_json(jsono_group_merge(patch ORDER BY event_ts)) AS state
FROM session_patch_events
GROUP BY session_id;

Use it as a window function to materialize the running state after each patch:

SELECT id,
       to_json(jsono_group_merge(patch ORDER BY id)
           OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS state
FROM (
    VALUES
        (1, jsono('{"theme":"dark"}')),
        (2, jsono('{"lang":"en"}')),
        (3, jsono('{"theme":"light"}'))
) AS t(id, patch)
ORDER BY id;

Result:

1  {"theme":"dark"}
2  {"lang":"en","theme":"dark"}
3  {"lang":"en","theme":"light"}

jsono_group_merge_max / jsono_group_merge_min

Order-independent variants of jsono_group_merge that take the ordering key as an ordinary second argument instead of an ORDER BY clause. Per leaf, the value from the row with the greatest order_key wins (jsono_group_merge_max) or the smallest wins (jsono_group_merge_min); null object members never overwrite, exactly like jsono_group_merge. They are drop-in replacements for the ordered form:

jsono_group_merge_max(value, order_key)  ≡  jsono_group_merge(value ORDER BY order_key)
jsono_group_merge_min(value, order_key)  ≡  jsono_group_merge(value ORDER BY order_key DESC)

WITH hits(params, event_ts) AS (
    VALUES
        (jsono('{"utm":{"source":"google"},"page":"/a"}'), 1),
        (jsono('{"utm":{"medium":"cpc"}}'), 2),
        (jsono('{"page":"/b"}'), 3)
)
SELECT to_json(jsono_group_merge_max(params, event_ts)) AS latest_wins,
       to_json(jsono_group_merge_min(params, event_ts)) AS earliest_wins
FROM hits;

Result:

latest_wins                                          earliest_wins
{"page":"/b","utm":{"medium":"cpc","source":"google"}}   {"page":"/a","utm":{"medium":"cpc","source":"google"}}

order_key is any comparable type; for a composite tie-break pass a ROW(...)/struct, which compares lexicographically:

SELECT to_json(jsono_group_merge_max(params, ROW(event_ts, hit_id)))
FROM hits;

Why a separate function? jsono_group_merge(value ORDER BY key) is order-dependent, so DuckDB buffers and sorts every input row before folding — O(rows) memory, which can exhaust memory on large groups. The keyed variants resolve conflicts per leaf by the key argument, so they are commutative and associative: DuckDB streams rows straight into the aggregate with no buffer and the state stays O(distinct leaves per group). Use these when folding a large stream (e.g. collapsing event hits into sessions) where the ordered form runs out of memory.

A shredded input keeps its shredding in the output, just like jsono_group_merge.

These functions require each path to keep a consistent kind across rows (always an object, or always a scalar/array). A path that is an object in one row and a scalar/array in another makes per-leaf last-write-wins order-dependent, so it is rejected with a clear error — use jsono_group_merge(value ORDER BY key) for such structurally-inconsistent data.

jsono_merge_patch

Applies one or more RFC 7396 merge patches left to right. The function is variadic and requires at least one argument; each null object member in a patch deletes that key from the result.

SELECT to_json(jsono_merge_patch(
    jsono('{"a":1,"b":2}'),
    jsono('{"b":null,"c":3}')
));

Result:

{"a":1,"c":3}

Pass additional patches to fold them in sequence:

SELECT to_json(jsono_merge_patch(
    jsono('{"a":1}'),
    jsono('{"a":2,"b":2}'),
    jsono('{"c":3}')
));

Result:

{"a":2,"b":2,"c":3}

Unlike jsono_group_merge, this is a scalar function over a fixed set of patch arguments rather than an aggregate over rows.

jsono_diff

Returns the structural diff of prev → cur: a minimal document containing only the keys/paths that changed. Each changed value renders by the cur-side type (the diff describes how to reach cur); a removed key (or a value cleared to null) renders as key: null. Changed scalars keep their native JSON type.

SELECT to_json(jsono_diff(
    jsono('{"status":"new","total":10,"note":"x"}'),
    jsono('{"status":"paid","total":10}')
));

Result:

{"note":null,"status":"paid"}

The optional arrays named parameter selects how an array-valued change renders (object/scalar handling is identical in all three modes):

arrays	an array change renders as
'atomic' (default)	the whole new cur array	order-sensitive; the exact inverse of jsono_merge_patch
'counts'	{"added": N, "removed": M}	order-insensitive multiset cardinalities
'elements'	{"added": [...], "removed": [...]}	order-insensitive multiset elements

SELECT to_json(jsono_diff(
    jsono('{"items":[{"sku":"a"},{"sku":"b"}]}'),
    jsono('{"items":[{"sku":"a"},{"sku":"c"}]}'),
    arrays := 'counts'
));

Result:

{"items":{"added":1,"removed":1}}

The default 'atomic' mode round-trips with jsono_merge_patch — jsono_merge_patch(prev, jsono_diff(prev, cur)) = cur (for cur with no explicit null at an object key, since null is the merge-patch delete sentinel). The 'counts' and 'elements' modes are change reports, not appliable patches. A NULL argument is treated as the empty document, so jsono_diff(NULL, cur) is "everything new" and jsono_diff(prev, NULL) is "everything removed" — handy for a lag() window over a snapshot stream:

SELECT seq, to_json(changed) AS changed
FROM (
    SELECT seq,
           jsono_diff(lag(snapshot) OVER (ORDER BY seq), snapshot, arrays := 'counts') AS changed
    FROM events
)
WHERE changed IS DISTINCT FROM jsono('{}');  -- drop no-op rows (a pure array reorder is also a no-op under counts/elements)

A number's representation is part of its identity, so 1, 1.0 and 1e0 compare unequal (a snapshot whose serializer drifts a number's form reports a change — normalize upstream if that matters).

jsono_group_array / jsono_group_object

Collect a group's values into a single JSONO array or object — the JSONO counterparts of core JSON's json_group_array / json_group_object.

-- Collect values into an array, in the ORDER BY fold order:
SELECT to_json(jsono_group_array(j ORDER BY ts))
FROM (VALUES (jsono('1'), 1), (jsono('"x"'), 2), (jsono('[3]'), 3)) t(j, ts);
-- [1,"x",[3]]

-- Collect key -> value pairs into an object:
SELECT to_json(jsono_group_object(k, j))
FROM (VALUES ('b', jsono('2')), ('a', jsono('1'))) t(k, j);
-- {"a":1,"b":2}

jsono_group_array keeps a NULL input row as a JSON null element (matching json_group_array) and returns SQL NULL for an empty group. jsono_group_object casts the key to VARCHAR and fails loud on a NULL key. Because JSONO stores unique sorted keys, jsono_group_object emits keys sorted and collapses duplicate keys to the last value in fold order — this diverges from json_group_object, which keeps every duplicate in input order. A shredded input is accepted (reconstructed to the whole document) and the result is always plain JSONO.

Introspection

jsono_type(value[, path])         -> VARCHAR    -- OBJECT/ARRAY/VARCHAR/BIGINT/UBIGINT/DOUBLE/BOOLEAN/NULL
jsono_keys(value[, path])         -> VARCHAR[]  -- object keys
jsono_array_length(value[, path]) -> BIGINT     -- array element count, NULL for a non-array
jsono_validate(value)             -> BOOLEAN    -- strict current-format validation
jsono_storage_size(value)         -> STRUCT     -- physical byte sizes (body blobs + shreds + total)
jsono_storage_type()              -> VARCHAR    -- DDL of the physical STRUCT backing JSONO
jsono_shred_manifest(value)       -> STRUCT[]   -- paths stripped into shred columns, with their types
jsono_version()                   -> INTEGER    -- current JSONO binary format version

jsono_type and jsono_keys inspect the shape of unknown JSON before extracting it with jsono_transform. The optional path is a constant JSONPath (same grammar as jsono_transform, without wildcards) that points at a nested position; a missing path yields NULL.

SELECT jsono_keys(jsono('{"user":{"name":"a","age":1}}'), '$.user');
-- [age, name]
SELECT jsono_type(jsono('{"items":[1,2,3]}'), '$.items[0]');
-- BIGINT

jsono_array_length returns the element count of the array at the root or at path. A non-array value (object, scalar, or JSON null), a missing path, and SQL NULL all yield NULL. This differs from core DuckDB json_array_length, which returns 0 for a non-array; jsono follows the NULL-on-mismatch convention of jsono_type/jsono_keys.

SELECT jsono_array_length(jsono('[10,20,30]'));
-- 3
SELECT jsono_array_length(jsono('{"items":[1,2,3,4]}'), '$.items');
-- 4
SELECT jsono_array_length(jsono('{"a":1}'));
-- NULL

jsono_validate checks the current binary format contract: header flags, sorted unique keys, slots, heaps, navigation metadata, UTF-8 strings, and raw number text. It returns false for malformed physical blobs and NULL for SQL NULL. On a shredded value it validates the residual encoding and cross-checks each non-NULL array-lane's length against the residual skeleton's element count (the lockstep framing every reader enforces, so it is jsono's to assert), returning false on a mismatch; the typed shred lane values are DuckDB-native and not jsono's to check.

jsono_storage_size reports slots, key_heap, string_heap, skips, shreds, and total byte counts. shreds is the per-row shred payload (0 for a plain value); total sums the body blobs and the shreds. It is a physical diagnostic and does not validate the value.

jsono_storage_type returns the DDL of the physical STRUCT that a JSONO value is — the form stored everywhere (DuckDB-native, DuckLake, plain Parquet). Use it to declare storage columns without hardcoding the field names; a column of that exact shape binds to jsono ops and to_json structurally, with no cast.

SELECT jsono_storage_type();
-- STRUCT(jsono STRUCT(body STRUCT(slots BLOB, key_heap BLOB, string_heap BLOB, skips BLOB, lengths BLOB, nums BLOB)))

jsono_shred_manifest returns a LIST<STRUCT(path VARCHAR, type VARCHAR)> of the paths a shredded value stripped out of its residual into shred columns, each with the shred type it was written as, in canonical (sorted) order. A plain value returns an empty list (nothing was stripped); a SQL NULL returns NULL. It reads the row's own manifest claim and, unlike the other readers, does not raise on a narrowed row — pair it with jsono_validate to debug shredding, layout, or migration questions (which paths got lifted, did a cast drop one). A shred value that did not round-trip its declared type stays in the residual and is correctly absent from the manifest, so a short manifest is not "shredding did not happen".

SELECT jsono_shred_manifest(jsono('{"a":"x","b":1}', shredding := {'a':'VARCHAR','b':'BIGINT'}));
-- [{'path': a, 'type': VARCHAR}, {'path': b, 'type': BIGINT}]

These helpers are primarily used by the JSONO workflows and tests. Treat their exact surface as less stable than jsono_transform, jsono, and to_json.

Benchmarks

bench/ is the comparable harness for jsono versions and the core DuckDB json baseline. Run a smoke benchmark:

uv run --frozen python bench/bench.py --filter group_merge/1k --runs 1

The operation set, version/core-json comparisons, field-sample scenarios, and result-reading contract are documented in bench/README.md; profiling is in bench/PROFILING.md.

Development

Run the full local pre-PR gate (release build + tests, assert-enabled build + tests, format check) in one command:

uv run make verify

Or run the gates individually:

uv run make release
uv run make test
uv run --frozen black --check bench

Useful commands:

uv run make debug
uv run make reldebug
uv run make clean
uv run --frozen python bench/run_benchmarks.py --list
uv run --frozen python bench/compare_results.py --save-baseline
uv run --frozen python bench/compare_results.py

The DuckDB submodule lives in duckdb/. Update it only via explicit submodule commands.

Configuration

JSONO_SHAPE_CACHE_SIZE controls the JSONO writer shape cache size:

JSONO_SHAPE_CACHE_SIZE=16384 ./build/release/duckdb -unsigned

Use power-of-two values when comparing performance. The default is tuned for local benchmark workloads, but real data can differ.

Error Handling and Caveats

• Object keys inside JSONO output are sorted, so serialized JSON text may not preserve input object key order.
• jsono_transform requires a constant STRUCT spec; its optional on_type_mismatch argument must also be constant and one of convert, null, or fail.
• jsono(value, shredding := spec) requires a constant STRUCT spec and rejects wildcard paths; object-key paths that round-trip through their shred type are physically stripped from the residual, while array-index paths and non-lossless shred values stay in the residual.
• Shredded values and core json interop: the jsono-named functions (jsono_extract, jsono_extract_string, jsono_type, jsono_keys, jsono_validate, jsono_storage_size), jsono_group_merge, the ::VARCHAR cast, and the shredded→plain JSONO reconstruct are bind-correct — they accept a shredded value directly and work even with the extension optimizer disabled. The operators and casts that resolve to the bundled core json extension — ->>, ->, json_extract, ::JSON, and to_json — are correct over a shredded value only with the extension optimizer enabled (the default): the optimizer rewrites them to read the shreds and residual. With the optimizer fully disabled (PRAGMA disable_optimizer) those core-routed operations bind into core json and serialize the physical fields instead. This is a structural limit: core json owns the STRUCT → JSON cast and DuckDB's cast registry keeps the first registration, so the extension cannot intercept it.
• Chained operator navigation (value -> 'a' ->> 'b') over a shredded column fuses to a single deep-path shred read, the same as value ->> '$.a.b'. Over a fully-constant jsono({…}) literal the same chain returns NULL: a wholly-constant expression is folded by DuckDB before the extension optimizer runs — through the same core STRUCT → JSON cast noted above — so the fuse never sees it. Real reads are over columns; for a constant literal use the JSONPath form jsono({…}) ->> '$.a.b' (which binds to a jsono function and folds correctly), not the chained operators.
• Declare a shredded storage column with CREATE TABLE … AS SELECT jsono(…, shredding := …) or from jsono_storage_type(<shred DDL>), and access a shred through ->>/to_json (the optimizer reads the shred) rather than by struct member name — .body and the shred fields are physical storage details. Inserting a value shredded on any other shred set — fully disjoint included — lands losslessly (the optimizer reshredds it to the column's shred set). A raw struct cast that drops or retypes a shred column (only possible without the extension optimizer) is caught at read time by the shred manifest: reading the narrowed row raises an error instead of silently losing the stripped value.
• jsono_extract / -> / jsono_extract_string / ->> require a constant path, do not support wildcard list extraction yet, and do not support negative array indexes.
• Unsupported scalar types in jsono_transform fail at bind time.
• JSON nested deeper than 1000 levels is rejected with an error (512 on macOS, whose worker-thread stacks are too small for the deeper recursion). Sanitizer builds lower the bound only where their fatter frames need it: 32 under AddressSanitizer (any platform) and under UndefinedBehaviorSanitizer on macOS; the UndefinedBehaviorSanitizer build on Linux keeps the full 1000.
• The binary JSONO format is strict-versioned (current version = 4, reported by jsono_version()). Incompatible storage changes must bump jsono::VERSION. A present-but-unreadable header — wrong magic, a different format version, misaligned slots — fails loud on read rather than silently reading as SQL NULL; only an absent value (a slots blob too short to hold a header) reads as NULL, and jsono_validate reports a corrupt blob as false.
• Malformed input raises DuckDB errors unless try_jsono is used. try_jsono never raises: implementation-limit violations on otherwise valid JSON (nesting past the depth limit, an oversized key or string) also map to NULL, indistinguishable from malformed input — use the strict jsono when a limit overflow must surface instead of dropping the row.

Known limitations

The current surface is intentionally small. The following are not implemented:

• No cast from JSONO to a scalar type (jsono('42')::INTEGER is unsupported). Project string values out with ->> / jsono_extract_string or typed fields with jsono_transform.
• No cast from JSONO to an arbitrary STRUCT. Only the physical six-BLOB shape is interchangeable with JSONO; field extraction goes through jsono_transform.
• No dedicated table function that unnests a JSONO array or object into rows. Use unnest(jsono_entries(...)) for flattened scalar leaves, or unnest(jsono_array_elements(...)) for array elements as rows.
• Object key order is not preserved: keys are stored and emitted in sorted byte order (see Error Handling and Caveats).

Contributing

Contributions and feedback are welcome. Please:

1. Open an issue first to discuss proposed changes.
2. Add or update SQLLogic tests in test/sql/ for new behavior.
3. Run uv run make verify (release build + tests, assert-enabled build + tests, format check) before submitting a pull request. CI additionally runs uv run make tidy-check, which needs a local clang-tidy + compile-database setup.

See GitHub Issues for current tasks and feature requests.

License

MIT. See LICENSE.

For third-party components and their licenses, see THIRD_PARTY_NOTICES.md.

---

This extension is based on the DuckDB Extension Template.