Using the JSONB Format Added in SQLite 3.45 Safely

SQLite stores JSON as text, and every function that reads it must parse that text first. Version 3.45.0 added an alternative: JSONB, a binary encoding of the same data that the JSON functions can read without parsing. It is a blob in an ordinary column, not a new column type, and every json_* function has a jsonb_* twin that returns it. It is smaller, faster to query and a bit more awkward to handle. This article shows how to produce it, how to read it, how much it saves on a 200,000-row table, how to convert an existing column, and the compatibility problems that appear the first time an older client opens the file.

What it is

JSONB is a sequence of bytes that describes a JSON value: each element has a header giving its type and its size, followed by the payload. hex(jsonb('{"a":1}')) printed 4C17611331: 4C is an object header with a length of four bytes, 17 61 is the text key a, and 13 31 is the integer 1. Larger documents use longer headers. Because sizes are in the headers, a reader can skip a whole subtree without scanning it, which is where the speed comes from.

Three properties follow from that design:

• It is a blob. typeof(jsonb('{"a":1}')) is blob. Printed in the shell it looks like garbage (L^Wa^S1), and in a database viewer it is a hex string. To see it, use json(): json(jsonb('{"a":1,"b":[1,2]}')) returned {"a":1,"b":[1,2]}.

• It keeps what the text had. Key order is preserved, and so are duplicate keys: json(jsonb('{"b":1,"a":2,"b":3}')) returned the same three members in the same order. This is not like PostgreSQL's jsonb, which sorts and deduplicates.

• It normalizes whitespace and syntax. Because it is parsed once, jsonb('{"a":1}') = jsonb('{ "a" : 1 }') was 1, while the same comparison on the texts was 0. JSON5 forms are read and converted: json(jsonb('{''a'':0x1F}')) returned {"a":31}, and jsonb('{a:1,}') yielded a valid blob that converts to {"a":1}.

The function pairs

For nearly every JSON function there is a binary twin. Arguments may be text or blobs in either; the difference is what comes back.

The scalar-returning functions are shared: json_type, json_valid, json_array_length, the arrow operators and json_extract (for scalars) work on both forms. What the twins differ in is the container result. json_extract(jsonb('{"a":[1]}'), '$.a') returned the text [1], and jsonb_extract of the same returned a blob. For a scalar value they agree: jsonb_extract(jsonb('{"a":1}'), '$.a') returned the integer 1. The arrow operators always return JSON text, even on a blob: jsonb('{"a":{"b":1}}') -> 'a' gave the text {"b":1}, so there is no JSONB arrow, and you wrap with jsonb() if you want a blob again.

An easy mistake is the reverse: calling json_set on a blob returns text, and jsonb_set on text returns a blob. typeof(json_set(jsonb('{"a":1}'), '$.b', 2)) was text. When you update a JSONB column, use jsonb_set, or the column silently flips back to text one row at a time:

update pb set attrs = jsonb_set(attrs, '$.price', 7) where id <= 20000;

Validity

json_valid(x) takes an optional second argument, a bit mask of acceptable forms: 1 for strict JSON text, 2 for JSON5 text, 4 for a blob that looks like JSONB, 8 for a blob that is strictly valid JSONB. With the default of 1, a JSONB blob is not valid: json_valid(jsonb('{"a":1}')) returned 0, and json_valid(jsonb('{"a":1}'), 8) returned 1. For a column of blobs, the constraint is check (json_valid(attrs, 8)). Flag 4 is the cheaper, looser test and flag 8 checks the whole structure. A blob that contains JSON text is accepted by the text flag: json_valid(x'7b2261223a317d'), the bytes of {"a":1}, returned 1, which means a text-JSON value stored in a blob column is read by the JSON functions as text. That mix is legal and confusing; keep each column to one form.

Size

The 200,000 documents:

form                       data bytes     table on disk
JSON text                  30.6 MB        35.5 MB
JSONB                      23.2 MB        24.5 MB
typed columns              (not applicable) 11.8 MB

The blob is 24 percent smaller than the text and the table about 31 percent smaller. JSONB drops whitespace, stores numbers as compact text and uses short headers instead of quotes and separators. Numbers are not stored in a numeric binary form, so a document of mostly numbers shrinks less than one of mostly strings. The typed table is a different design and shown for scale.

Speed

Parsing is the main cost of reading JSON text, and JSONB skips it. Two queries, each run twice on the text table and the blob table:

filter: attrs ->> '$.specs.dims.w' = 20          text 121-191 ms     JSONB 42-54 ms
group by brand, avg(price)                        text 326-407 ms     JSONB 136-148 ms
four json_extract calls per row, summed           text 199 ms         JSONB 120 ms
exists (json_each over the tags array)            text 145 ms         JSONB 78 ms

Two to three times faster across the board. It is a faster scan, not an index, and the same rules apply: an expression index decides whether the query seeks or scans. A full scan of the blob table is still a scan of 200,000 rows. When the queries are selective, put an expression index on the extraction (indexing JSON fields in SQLite). When they are not, JSONB makes the scan cheaper.

Writes are cheaper as well, as an update has less text to build: updating the price in 20,000 documents took 0.14 s on the text table and 0.08 s on the blob table, both without JSON indexes.

Converting a column

Converting is an update, followed by a vacuum to give back the space:

update m set doc = jsonb(doc);
vacuum;

On 200,000 documents the update took about 1.5 seconds. Afterward the file was still 32.5 MB, because freed pages stay in the database file; vacuum brought the table down to 24.4 MB. typeof(doc) then showed blob for all 200,000 rows, json_valid(doc, 8) = 0 matched no rows, and json(doc) returned the original text, minified. Steps for a live system:

1. Add the application support first: every reader must be able to handle blobs, either through SQLite's own functions or through json().

2. Convert in batches (by id range) inside transactions, so a failure leaves a consistent table.

3. Validate with json_valid(doc, 8) after the conversion.

4. vacuum or vacuum into 'new.db' during a quiet period. vacuum needs temporary space of the size of the database.

5. Update the CHECK constraint, which cannot be altered in place in SQLite: it takes a table rebuild.

Keep a copy of the original text for a while; the conversion is not information-preserving for whitespace, number formats or JSON5 spellings.

Compatibility

JSONB is a file-level and function-level feature. The database file stays readable by any SQLite 3.x, since the blobs are ordinary blobs. The functions are the problem. In the container's Python:

select jsonb('{"a":1}')                           OperationalError: no such function: jsonb
select json_pretty('{"a":1}')                     OperationalError: no such function: json_pretty
select json_extract(attrs, '$.sku') from pb ...   OperationalError: malformed JSON

Python's sqlite3 module links to whatever library the operating system provides, in this case 3.40.1. It knows the arrow operators ('{"a":1}' ->> 'a' returned 1) and not JSONB, and when it meets a JSONB blob, it treats the bytes as text and reports malformed JSON. The same applies to any language binding that uses a system library, to older command-line shells and to GUI tools. The fix is to bundle a recent SQLite, to upgrade the platform, or to keep text in the column and convert at the edges. Some applications read blobs in their own code: the bytes the driver returns for a JSONB column are the raw encoding (b'\xccn7sku\xa7SKU-0' was the start of one document), which no JSON parser can read.

Other tools that read SQLite files, such as backups inspected with a viewer, sqlite3 shells older than 3.45 and data exports, see hex. If someone needs to read a JSONB column by hand, the answer is select json(doc) from m, in a shell new enough to run it. Plan for that before the day it matters.

When to keep plain JSON text

Other tools read the database. Any pipeline, report or person using a viewer that does not know JSONB sees blobs.

Your SQLite is older than 3.45 somewhere. One old client turns every query on the column into an error.

The data is small or rarely queried. A 3-fold speedup of a 1 ms query is nothing. The migration cost is real.

You need the exact text back. JSONB normalizes whitespace and number spellings. For signatures and byte-exact audit records, store the original text.

You are weighing it against typed columns. JSONB closed about half of the gap in my tests; typed columns were still three times faster than JSONB and half its size. For fields you query constantly, promote them (JSON functions in SQLite has the full comparison).

The format is specified in the SQLite documentation, The JSONB Format, and the functions in JSON Functions And Operators. The loading side is covered in loading a JSON file into SQLite with readfile.