JSONB, Internal Representation, and Performance Tradeoffs

Learning outcomes

Text JSON is portable and inspectable. SQLite JSONB is an optimization-oriented representation for applications that intentionally keep JSON processing inside SQLite. The right comparison is measured cost and interoperability—not a claim that one format is categorically superior.

JSONB is SQLite's internal parse-tree representation

Beginning in SQLite 3.45.0, a JSON value can be stored as a BLOB containing SQLite's internal binary representation. JSON functions that accept text JSON also accept valid JSONB. Because SQLite can skip parsing text into its internal representation, JSONB can reduce CPU work for many operations and commonly consumes somewhat less space.

SELECT sqlite_version() AS sqlite_version,       typeof(jsonb('{"a":1}')) AS jsonb_storage_class,       length('{"a":1}') AS text_bytes,       length(jsonb('{"a":1}')) AS jsonb_bytes;

On a supporting build, the JSONB storage class is blob. If jsonb() does not exist, keep using text JSON and skip the benchmark; do not install an untrusted native extension just to complete the lesson.

SQLite JSONB is not PostgreSQL JSONB

The name is intentionally similar, but the binary formats are not compatible. Current SQLite documentation also makes no O(1) element-lookup promise. Most SQLite JSONB operations remain O(N), like text JSON. The current advantage is principally avoiding parse/render work and often reducing representation size.

Round-trip through APIs, never through a hex editor

Applications should create JSONB with SQLite and convert back to canonical text with SQLite when interchange is needed. This keeps the private binary format behind a supported interface.

WITH sample(text_doc) AS (  VALUES ('{"firmware":{"version":"3.7.2"},"tags":["pump","critical"]}')), encoded AS (  SELECT text_doc, jsonb(text_doc) AS binary_doc FROM sample)SELECT typeof(binary_doc) AS storage_class,       json(binary_doc) AS round_trip_text,       json_extract(binary_doc,'$.firmware.version') AS firmware_versionFROM encoded;

The extracted result should be the same logical value as text JSON. The exact bytes of binary_doc are intentionally not part of the application contract.

Validate JSONB at the boundary

Current two-argument json_valid() accepts flags describing which representation is allowed. Bit 0x04 performs a fast superficial JSONB check; 0x08 performs a deeper linear-time check. SQLite documentation recommends the lighter check for most purposes. A flags value of 6 accepts JSON5 text or probable JSONB and is a useful “can SQLite JSON routines plausibly consume this?” test.

WITH sample(b) AS (SELECT jsonb('{"ok":true}'))SELECT json_valid(b, 4) AS probable_jsonb,       json_valid(b, 8) AS strict_jsonb,       json_valid(b, 6) AS json5_or_jsonbFROM sample;

For a column contract that promises canonical TEXT JSON only, continue to use one-argument json_valid(metadata). Do not broaden validation just because more flags exist.

Malformed JSONB follows garbage-in/garbage-out rules

SQLite-generated JSONB is well formed. If an application fabricates or corrupts BLOB bytes, a JSON query may abort, return a correct answer when the damaged region is irrelevant, or return nonsense. SQLite explicitly promises malformed JSONB will not become a memory-safety vulnerability, but that promise is not a reason to manipulate the format manually.

Measure size on your own documents

Use the same logical documents in text and JSONB columns so the comparison is fair. Length differences vary with keys, strings, numbers, and nesting.

CREATE TABLE json_bench (  id INTEGER PRIMARY KEY,  doc_text TEXT NOT NULL CHECK(json_valid(doc_text)),  doc_jsonb BLOB NOT NULL);WITH RECURSIVE seq(n) AS (  VALUES(1) UNION ALL SELECT n+1 FROM seq WHERE n < 20000)INSERT INTO json_bench(id, doc_text, doc_jsonb)SELECT n,       json_object(         'device',printf('SENS-%05d',n),         'firmware',json_object('version',printf('3.7.%d',n%10),'channel',iif(n%5=0,'beta','stable')),         'metrics',json_object('temperature',20+(n%17)*0.25,'vibration',(n%23)*0.03),         'tags',json_array('sensor',iif(n%2,'north','harbor'))       ),       jsonb(json_object(         'device',printf('SENS-%05d',n),         'firmware',json_object('version',printf('3.7.%d',n%10),'channel',iif(n%5=0,'beta','stable')),         'metrics',json_object('temperature',20+(n%17)*0.25,'vibration',(n%23)*0.03),         'tags',json_array('sensor',iif(n%2,'north','harbor'))       ))FROM seq;SELECT round(avg(length(doc_text)),1) AS avg_text_bytes,       round(avg(length(doc_jsonb)),1) AS avg_jsonb_bytesFROM json_bench;

During course validation on SQLite 3.46.1 with a related 20,000-row document shape, average values were about 212.7 bytes for text and 173.5 bytes for JSONB. That is an observation from one dataset, not a guaranteed ratio.

Measure extraction work rather than promising speed

Use the same query and result over both representations. Run multiple times, alternate order, and consider cache effects. The command-line .timer on is useful when a current sqlite3 shell is available, but application benchmarks should measure through the actual driver and workload.

SELECT sum(json_extract(doc_text,'$.metrics.temperature'))FROM json_bench;SELECT sum(json_extract(doc_jsonb,'$.metrics.temperature'))FROM json_bench;

In the course validation environment, a related benchmark produced the same result and a lower median execution time for JSONB (roughly 3.2 ms versus 10.0 ms across seven cached runs). Hardware, SQLite build, document shape, cache state, and query mix can all change that relationship. The lesson is to measure, not memorize those numbers.

Interoperability can dominate micro-performance

A JSON text column can be inspected by non-SQLite tooling, exported directly, diffed, logged, and transmitted as standard JSON. A JSONB BLOB should normally be converted with json() before leaving SQLite. If several services, languages, analytics tools, or migration systems directly consume the database, text may be the more robust contract even when JSONB is locally faster.

Version-aware optional lab

JSONB requires SQLite 3.45.0+. The newer jsonb_each()/jsonb_tree() traversal functions require 3.51.0+. A database can therefore support JSONB storage while lacking those newer table-valued variants. Capability detection should test the feature actually used.

SELECT sqlite_version();SELECT typeof(jsonb('{}')) AS jsonb_available;-- Current 3.53.4 supports this; older JSONB-capable builds may not:SELECT key, typeFROM jsonb_each(jsonb('{"a":1,"b":2}'))ORDER BY key;

If the last statement fails on a 3.45–3.50 build, that does not mean JSONB itself is unavailable.

Verification checkpoint

Production judgment and bridge

Representation can reduce JSON processing cost, but it does not solve the harder access-path problem. Lesson 5 promotes selected stable JSON properties into generated/indexed expressions and uses the planner to prove when an index is actually used.

Authoritative references

• SQLite JSONB documentation
• SQLite json_valid() flags
• SQLite release history