SQLite keeps JSONB values binary during traversal

Storing a parsed JSON representation loses some of its benefit if an intermediate operation turns a nested object back into text. SQLite 3.51.0, released on 4 November, adds jsonb_each() and jsonb_tree() to address that boundary. They retain the existing traversal behavior while returning JSONB in the value column for arrays and objects.

The qualifier about arrays and objects is essential. These functions do not turn every scalar result into a binary object. A traversal still exposes SQL values for scalar elements. The difference appears where a row represents a container that can become the input to another JSON operation.

I like SQLite's ability to combine structured SQL with a file owned by the application. JSON support is useful within that model when a document has meaningful structure that does not justify separate columns for every field. Keeping a container binary through an intermediate query is a concrete improvement to that use case.

The earlier JSONB change supplied the representation

SQLite 3.45.0, released on 15 January 2024, introduced the serializable internal parse-tree format called JSONB. Its purpose includes avoiding repeated parsing when the representation is stored and reused. November's change extends the ways a query can retain that representation while breaking a document into rows.

The JSON implementation at the 3.51.0 tag explains how this works. A JSONB element has a header and a payload. The header identifies the element type and encodes its payload length. Arrays and objects contain further elements, so their binary representation preserves the nested structure without requiring the textual delimiters to be parsed again.

This is SQLite's own encoding. The source explicitly says it is incompatible with PostgreSQL's JSONB format. Sharing a name does not make a blob portable between those engines. A transport format intended for systems outside SQLite needs a representation those systems understand, which remains a different decision from using JSONB internally.

The source also states that decoding and interpreting JSONB remains linear in the size of the input. The format reduces parsing overhead. It does not promise constant-time lookup for arbitrary properties. That distinction prevents a useful representation improvement from becoming an unsupported claim about a new indexing structure.

The value of the November functions follows from those properties. If a query extracts a nested container only to give it to another SQLite JSON function, converting it to JSON text introduces a representation change that the next operation may have to reverse. Returning JSONB can avoid that conversion at the intermediate boundary.

Traversal depth and result representation are separate choices

The release gives two new names because the existing traversal operations have different scopes. json_each() iterates immediate children, while json_tree() traverses the hierarchy. Their JSONB counterparts preserve those respective behaviors. Choosing the binary-returning function does not change an immediate-child traversal into a recursive one.

The tagged code implements this through shared virtual-table machinery. The column-producing function returns a value according to the cursor's mode. It reports element type separately, and the atom column supplies scalar values while container rows do not have a scalar atom. The result can therefore mix ordinary SQL scalar values with binary container values in the same value column.

For example, this query examines two children of an object. One child is an array and the other is an integer. It is intended for the new functions in 3.51.0.

SELECT key, type, typeof(value) AS storage_type
FROM jsonb_each('{"items":[1,2],"count":2}')
ORDER BY key;

The count row has JSON type integer and SQL storage type integer. The items row has JSON type array and SQL storage type blob. Supplying text in this example also demonstrates that the new name describes container output behavior rather than a requirement that callers supply an existing JSONB blob.

This distinction affects application adapters. A consumer expecting every array or object value from traversal to arrive as readable JSON text must account for the new output representation. Merely changing the function name can change the type handed to the host language, even when the represented document is identical.

For an SQL pipeline that continues processing containers, that is the intended benefit. For a response that sends a document to a browser, readable JSON text remains a separate output requirement. The decision belongs at the boundary where the value leaves SQLite's JSON machinery, because the internal encoding is not a general replacement for JSON interchange.

A recursive walk still contains container rows

The tree variant is especially useful for understanding why the release describes only some values as JSONB. A recursive traversal can include an object, an array inside that object, and scalar descendants. Those rows represent different kinds of JSON element. Their shared table schema does not imply that every element needs the same SQL representation.

A query looking for scalar leaves can use the type or atom information to distinguish them. A query retaining subdocuments can use the container values. The new functions preserve that choice while changing how those retained containers are represented. This makes the result useful for both relational filtering and further document processing.

It also means a blob is not evidence that the traversal failed to decode the input. For an array or object row from jsonb_tree(), binary output is the specified behavior. An inspection tool needs to interpret the type column alongside the value instead of assuming that non-text output is malformed JSON.

Extension code arrives without default activation

Another 3.51.0 change moves carray and percentile into the amalgamation. Both remain disabled by default. The release names SQLITE_ENABLE_CARRAY and SQLITE_ENABLE_PERCENTILE as the compile-time options that activate them. This changes distribution of the code without making either capability universal in every library reporting the new version.

The distinction matters for embedded deployments. One application's library can include percentile support while another library built from the same release omits it. A version check establishes a source release, but build configuration still determines optional capabilities. The amalgamation reduces the need to gather separate source files without removing those choices.

For percentile, the release-tag implementation gives a precise contract. percentile(Y,P) takes a percentage between zero and one hundred. It ignores NULL input values, rejects nonnumeric non-NULL values, and requires the percentage to remain consistent across the aggregate. median(Y) is the corresponding fiftieth percentile.

Those functions are useful only when their input and output semantics match the calculation required. A percentile is not an average, and an interpolated percentile need not equal an observed sample. The implementation defines the result by ordering the non-NULL values and interpolating at the requested position. That choice belongs in the explanation of a report using the function.

Percentiles make the resource tradeoff visible

The percentile implementation accumulates values in an array of doubles and sorts them for ordinary aggregate computation. Its comments describe runtime proportional to the number of inputs times the logarithm of that number. Storing all the input values also means this is not a constant-memory streaming approximation.

For instance, a build with percentile enabled can evaluate a deliberately small set of values as follows.

WITH samples(value) AS (VALUES(10), (20), (40), (50))
SELECT median(value), percentile(value, 50)
FROM samples;

Both expressions produce thirty. The result lies between the two middle values, which makes the interpolation visible without a large dataset. The example is not a claim about a production latency distribution or a measured workload. It specifies the calculation on four known inputs.

The implementation also offers continuous and discrete percentile variants. The discrete variant returns an input value rather than the interpolated result. Standard ordered-set syntax has a separate compile-time option, so activating percentile support alone should not be described as enabling every spelling used by other SQL systems.

I prefer these capabilities to remain explicit build decisions. An application that needs document traversal can use the new JSONB operations without assuming it has acquired every optional analytics function. Another application can include exact percentile calculations and accept their storage and sorting costs. The release makes both integrations easier to package while keeping their behavior and activation requirements distinct.

For JSON processing, the immediate change is a more consistent binary path through container traversal. The database can store JSONB, expose a nested array or object as JSONB, and pass that value into another operation without requiring an intermediate textual representation. At the application boundary, callers still choose whether they need an SQL scalar, a SQLite binary container, or JSON text for interchange.