SQLite's JSONB reads 3 to 15 times faster, until you ask for the text back
Since 3.45, SQLite can store JSON in a binary form, JSONB, and every json_* function accepts it. I stored the same documents both ways in SQLite 3.46.1 and timed json_extract on a single key. JSONB was 3x faster on small documents and up to 15x faster on big ones. Its speed also depended on where the key sits in the document, which the text form's speed did not. Getting a whole document back out as text costs as much as parsing it in the first place, so JSONB only pays when you query inside documents more than you return them whole.
The test
Flat objects with 10, 100 or 1,000 keys, two thirds integers and one third short strings, about 2 million keys per table in total. Everything ran in an in-memory database, so the numbers measure CPU only, not I/O. Each query summed one key over every row, best of 5. I ran it 3 times; the ratio ranges below cover all three runs. Times are nanoseconds per row, from one run.
| keys per doc | key read | text | JSONB | ratio (3 runs) |
|---|---|---|---|---|
| 10 | first | 593 | 107 | 2.2–5.5x |
| 10 | last | 534 | 166 | 3.2–3.6x |
| 100 | first | 3,438 | 493 | 5.2–7.0x |
| 100 | middle | 3,383 | 962 | 3.5–4.7x |
| 100 | last | 3,911 | 1,087 | 3.2–3.6x |
| 1,000 | first | 29,344 | 2,062 | 10–14x |
| 1,000 | middle | 28,726 | 5,202 | 5.5–6.5x |
| 1,000 | last | 34,424 | 6,494 | 4.5–5.5x |
On the text column the key's position barely matters, because SQLite parses the whole document before it looks anything up. JSONB skips that parse, but finding a key is still a walk past the keys that come before it, since there's no index inside an object. In the 1,000-key documents the last key costs about 3x what the first key does. If one or two fields are what you filter on, put them first when you build the document.
Updates gain more than reads. jsonb_set on one key was 5x faster than json_set at 10 keys and 13–16x faster at 1,000 keys, because the text version parses the document and then writes out the whole thing as text again.
The catch
Your application almost certainly wants text. Turning a JSONB value back into text with json() took 298 ns per row at 10 keys and 21 µs at 1,000 keys. That's about what jsonb() takes to parse the text in the first place (506 ns and 26 µs). If most of your queries look like SELECT doc FROM t WHERE id = ?, a JSONB column makes every read pay that conversion, and the parse it saves happens nowhere, because a plain text column is returned as it is stored.
JSONB also took up 23% less space here (141 vs 183 bytes at 10 keys, 15.2 vs 19.5 KB at 1,000). The saving comes from numbers and punctuation; long strings would shrink it.
So what
If you filter, aggregate or patch inside documents in SQL, store JSONB: it's several times faster and a bit smaller. If you mostly store documents and hand them back whole, keep text. Converting is cheap to try: UPDATE t SET doc = jsonb(doc), and the same queries keep working.
Method: one machine, a 40-line Python script, SQLite 3.46.1 in a container, 2 CPUs per run. Documents are synthetic and flat; nested paths would add a walk per level.