James Ainslie

Repeatedly extracting a field from a JSON document can mean repeatedly interpreting the same punctuation. The document has not changed, but the database still needs a representation it can navigate. SQLite 3.45.0, released on January 15, makes that representation something an application can retain. Its JSONB values store the internal parse representation as database BLOBs, so subsequent operations can begin with the parsed document.

The release announcement describes a rewrite of the JSON functions around a serializable internal format. This changes the cost of working with JSON without adding a separate document database to an application. I like this direction because SQLite's appeal is a capable SQL engine inside the process, with a documented file format underneath it. JSONB extends the work that engine can retain between calls.

Parsing becomes a storage decision

SQLite already has a substantial JSON interface. The 3.38.0 release made JSON functions part of the default build and introduced the -> and ->> operators in February 2022. The new release addresses a different problem. Applications could already ask SQL questions about JSON text, but keeping text as the stored representation meant that later operations still had to interpret that text.

The implementation's versioned source explains the distinction. Each JSONB element contains a header and a payload. The header identifies the element's type and records its size. An interpreter can advance over an element using that length, instead of finding its closing delimiter while obeying JSON quoting and escape rules. That makes navigation cheaper even though the logical document remains the same.

This is a narrower and more useful claim than saying that binary data is inherently fast. The saved work comes from retaining information that parsing had already established. The stored value records where an element ends and what kind of element it is. Reading the value no longer requires deriving all that information again from punctuation.

The format description, published before this release, also explains why the representation does not convert every payload into a machine-native value. Numeric payloads retain textual representations. Different string encodings record whether escaping or translation is necessary. Conversions can wait until an operation actually needs them. A binary representation can therefore reduce interpretation costs without eagerly normalizing every value in the document.

Using the representation through SQL

JSONB is an SQLite BLOB, not a new SQL storage class. The JSON functions understand that BLOB, and corresponding jsonb_ functions can return it. Applications should use those functions to construct and inspect values. The format documentation explicitly discourages manipulating the bytes directly, even though it documents the encoding for compatibility.

An illustrative table can retain a parsed document and extract an ordinary SQL value from it. The function registrations and extraction implementation in the release's source establish the behavior used here.

CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    body BLOB NOT NULL
);

INSERT INTO documents(id, body)
VALUES (1, jsonb('{"status":"queued","attempts":0}'));

SELECT json_extract(body, '$.status')
FROM documents
WHERE id = 1;

The insert translates JSON text into JSONB once. Extraction accepts that stored representation and returns the selected string as an SQL value. The schema's BLOB declaration alone does not establish that the contents are valid JSONB. Constructing the value with jsonb() supplies the representation expected by the JSON functions.

An application that needs text at an external boundary can ask SQLite to render it. Keeping a document in JSONB does not require exposing its binary encoding to an HTTP client or another program. Conversion remains explicit at the point where a textual representation becomes useful.

SELECT json(body)
FROM documents
WHERE id = 1;

This arrangement suits a document that the application repeatedly inspects or edits inside SQLite. A document stored only to be returned unchanged has a different cost distribution. Converting that document on entry and again on exit creates work that a text-only path might avoid. The mechanism explains where the benefit comes from, so the decision can follow the document's actual operations.

JSONB does not add an index inside every object

The source describes decoding and interpretation as linear in input size, as with text JSON. Length headers reduce the amount of work required to traverse the representation. They do not turn every object field access into an indexed lookup. A large object can still require navigation through preceding content before SQLite reaches the requested member.

That distinction affects schema design. Replacing JSON text with a JSONB BLOB does not by itself make a predicate over millions of documents selective. The per-document interpretation cost and the number of documents visited are separate concerns. Saving parsing work can help a scan, but it does not establish a new access path to the matching rows.

I prefer describing this release as persistence for parsed JSON because that wording keeps the promise precise. It is easy to hear a familiar format name and assume a larger collection of database features. The change here has a specific implementation and a specific source of savings.

The encoding also differs from PostgreSQL's JSONB. SQLite's source explicitly says the formats are incompatible. A BLOB produced by one system is not a transport format for the other. Applications exchanging JSON between databases still need an agreed representation, and ordinary JSON text provides the relevant boundary for this example.

Validation has an explicit policy

The release adds a second argument to json_valid(). Its purpose is to specify which representations count as valid, which becomes necessary when JSON text and binary JSON can both be legitimate inputs. The versioned implementation gives the flags concrete meanings and preserves the one-argument function's default policy.

The flag value 4 asks for a superficial JSONB check. The flag value 8 asks for a thorough check of the representation. They answer different questions. A value can have an outer header and length that look plausible without every nested element being correct. A full walk costs more because it examines the internal structure.

SELECT json_valid(jsonb('{"ready":true}'), 4);
SELECT json_valid(jsonb('{"ready":true}'), 8);

Both expressions accept the JSONB constructed by SQLite. Their different policies matter when a BLOB arrives from somewhere other than a trusted constructor. Choosing the superficial policy means accepting its narrower evidence. A full validation policy examines the document more deeply before later operations rely on it.

The same source assigns separate flags to canonical JSON text and JSON5 input. Validation therefore describes an application's accepted language and representation, rather than serving as an unspecified test for anything that resembles JSON. Calling the default one-argument function on a binary value is not equivalent to requesting JSONB validation.

The format preserves conversion choices

The encoding has more than one string type because strings can originate in canonical JSON, JSON5, or SQL values. Some payloads contain escapes that need interpretation when extracted. Others can be used without that translation. Retaining those distinctions avoids doing conversion work merely because a value passed through the parser.

Numbers follow the same general principle. A numeric payload can preserve its text until a consumer needs a numeric interpretation. That design is particularly relevant when a document contains fields that a query never touches. Eagerly translating every field would charge the operation for values it does not use.

The documented headers also allow a payload length to occupy more space than its smallest possible encoding. The format description gives construction as the reason. A producer can reserve room for a length before it knows the completed size, then fill in the header afterward. The representation supports practical document construction as well as subsequent reading.

SQLite's published compatibility intent for JSONB is that database files should not require an export and reimport simply because a newer SQLite understands the stored representation. That is a persistence requirement, even though applications should treat the individual BLOB as opaque. I value that separation because a stable stored representation and a private application implementation are compatible goals. Programs can depend on SQL functions while SQLite takes responsibility for interpreting the bytes.

For an application that repeatedly works on the same JSON document, 3.45.0 adds a concrete choice about where parsing belongs. Text can remain the exchange representation, and the database can retain the representation used for subsequent SQL operations. The benefit depends on how often those operations reuse the parsed structure before the document needs to become text again.

The size claim also needs that implementation context. SQLite describes JSONB as usually somewhat smaller than the equivalent text, while acknowledging cases where it can be larger. Removing delimiters saves bytes, but element headers consume bytes too. Documents with different shapes will distribute those costs differently. Storage savings are a consequence of the encoding, not a fixed compression ratio that an application can assume for every document it receives.