A JSON column is a schema decision: measuring 50,000 orders in SQLite and D1

An order with three lines. There are two ways to keep it in SQLite:

CREATE TABLE order_items(order_id INTEGER, line_no INTEGER, sku TEXT,
                         qty INTEGER, price_cents INTEGER, title TEXT,
                         PRIMARY KEY(order_id, line_no));

CREATE TABLE orders(id INTEGER PRIMARY KEY, placed_at TEXT, customer TEXT,
                    status TEXT, items TEXT NOT NULL);   -- JSON array

The second one is easier to write code against. You fetch an order, you have the order, lines included. No join, no ordering column, no "did I forget a line" bug. Every tutorial that introduces a JSON column says some version of this, and it is true.

What none of those tutorials say is what the convenience costs at the specific points where a shop actually reads its own data: the daily total, "which orders contain this item", "bump this price", and the filter that quietly returns every row instead of some of them.

So I built the same 50,000 orders both ways and measured. 150,000 line items, 2,000 SKUs, ten product titles, 12,000 customers, three lines per order. Same rows, three shapes: normalised tables, one JSON text column, and one JSONB column. Then the same queries against each, on SQLite 3.46.1 in process (Python 3.12.16) and, where the engine allowed it, on Cloudflare D1 read-only. Every number below is from this run; the scripts are listed at the end.

The short version: reads of one order cost the same in both shapes, which is why the document feels free. Everything that reads or writes across orders does not, and the largest multiplier is not on a slow query — it is on a query that is not allowed to be fast.

What the three shapes weigh

The document is bigger, and the reason is boring: keys. A line in the document is

{"sku":"SKU-00777","qty":2,"price_cents":990,"title":"Espresso beans 1kg"}

— 75 bytes, against 39 for the same line as a row. The 36-byte difference is almost entirely the key names and their quote-and-colon punctuation. Fifty thousand orders × three lines means SQLite stores the word price_cents 150,000 times. Normalised, it is a column header once.

JSONB, which arrived in SQLite 3.45, halves that penalty without giving up the document: it stores the parsed structure rather than the text, so keys and separators are not repeated verbatim. Payload measured 10,685,381 B as text and 9,025,296 B as JSONB — 15.5% smaller, and 1.13× the size of the two tables.

One thing worth noticing before moving on: building the document was not slower. Inserting 50,000 orders took 0.674 s normalised and 0.651 s as documents (0.979 s for JSONB, which pays a conversion per insert). The write cost people expect from documents did not appear at build time. It appears later.

Reading one order is genuinely free

This is the measurement that makes the document attractive, and it is real: one row by primary key, one parse of 227 bytes. If all your code ever did was load one order and show it, the rest of this post would not matter.

Reading across orders: 2.9×, and one query at 10.9×

Now the same data, asked for a total.

All three agree on the answer — the grand total is 551,550,980 cents in every shape — but the document pays for 150,000 expansions of a virtual table. From the second row you can divide: 0.10 µs per row versus 1.11 µs per JSON element, roughly eleven times the work per line.

json_each is a virtual table, and that phrase is doing real work. It does not have statistics, an index, or a stored row order. Reading it materialises one row per array element, per parent row, and the aggregating half of the plan cannot start until the expanding half has produced something:

SCAN orders
CORRELATED SCALAR SUBQUERY 1
SCAN json_each VIRTUAL TABLE INDEX 1:

Two of those three lines are scans. On the normalised side the equivalent plan reads a 150,000-row table once — no expansion, no per-row subquery.

The cost also scales with the document, not with the data you want:

Linear at about 0.89 µs per line. That is fine if a document is three lines and fatal if it is a thousand, and it is the reason "just put the menu in a JSON column" and "just put the order book in a JSON column" are different decisions wearing the same clothes.

The query you are not allowed to make fast

Here is the one that decides the architecture for most shops: which orders contain this SKU?

Normalised, with an index on sku:

SELECT DISTINCT order_id FROM order_items WHERE sku = 'SKU-00777';
-- SEARCH order_items USING INDEX idx_items_sku (sku=?)

1,090×. Not because the JSON version is badly written — it is the only version available. A JSON array inside a column has no index on its elements; the query has to open all 50,000 documents and expand all 150,000 elements to answer a question about one SKU. And notice the middle row: the same query on the same normalised table, merely unindexed, is 14 ms. The document version is 8.5× slower than the correct SQL without an index, and 1,090× slower than the correct SQL with one.

That is the honest shape of the trade. Documents do not remove the need for an index; they remove the ability to have one.

Writing: 11×, 41×, 35×

"Partial update" is the promise that makes documents feel cheap. It is not a partial write.

-- the document version, "in place"
UPDATE orders
   SET items = json_set(items, '$[1].price_cents',
                        json_extract(items, '$[1].price_cents') + 1);

-- the normalised version
UPDATE order_items SET price_cents = price_cents + 1
 WHERE order_id = ? AND line_no = 2;

json_set re-parses the whole document to find the path, rebuilds the whole document to return it, and the row written back is the whole 227 bytes — against a 39-byte row on the normalised side, 5.8× the bytes for one field. Appending and removing are worse than replacing because the array itself changes length, and the result is that a one-line edit to a 10,000-order set costs five seconds.

There is a caveat I owe you: single-row commits on this filesystem cost about 40 ms each regardless of shape (one-line update measured 39.1 ms normalised and 42.5 ms as a document, including the recompute of a materialised total). At that granularity the shape disappears into the fsync. That is why the table above is measured in bulk: the bulk numbers are the ones that show what the storage engine is doing.

Three middle paths, and what each one buys

A document column does not have to be all-or-nothing. Each of these is cheap to add and gets back part of what normalisation gives you — measured on the same 50,000 orders.

1. An index on an expression. One JSON path can be indexed:

CREATE INDEX idx_first_sku ON orders(json_extract(items, '$[0].sku'));
-- SEARCH orders USING INDEX idx_first_sku (<expr>=?)

310×, for 0.88 MB of index over 50,000 documents. The catch is in the path: it indexes the first line's SKU and nothing else, so it answers a different question than the one you probably have. Which brings us to a rule worth writing on the wall: json_extract(items,'$.sku') on an array is not an error. It returns NULL for every row, because $.sku looks for a key named sku on the array itself. Index or no index, that query returns 0 rows and raises nothing.

2. A materialised column. If you filter on an order total, stop computing it from JSON at read time:

ALTER TABLE orders ADD COLUMN total_cents INTEGER;  -- maintained on write
CREATE INDEX idx_total ON orders(total_cents);

1,728×. The price is on the write side, where it belongs to you: the backfill of 50,000 documents took 28.2 s (0.57 ms each), and every later line edit has to recompute that total or it drifts. I checked the drift: after the backfill, 0 of 50,000 rows had a stored total different from the recomputed one. That check is four lines of SQL and I would put it in a scheduled job, because a materialised column is a copy, and copies rot silently.

3. A generated column, when the value is a pure function of the document. json_array_length(items) or a single extracted field can be a stored column with an index on it, and the engine keeps it consistent:

CREATE TABLE orders(id INTEGER PRIMARY KEY, items TEXT NOT NULL,
                    lines INTEGER GENERATED ALWAYS AS (json_array_length(items)) VIRTUAL);
CREATE INDEX i_lines ON orders(lines);   -- SEARCH orders USING INDEX i_lines (lines=?)

What a generated column cannot do is aggregate: sum over json_each is not allowed in a generated column, so the total above has to be a real column that your code maintains. That asymmetry — counts and single fields are free, sums are your job — is the actual boundary between "document with normalised edges" and "normalised with a document".

Where documents lie, and the count that proves it

Every failure in this section ran without an error message. That is the point of collecting them together.

Types change when a value goes through JSON. What comes back from json_extract is a value with an SQLite type, not a generic number:

json_type tells null from missing; typeof does not. If "price is missing" and "price is null" have different meanings in your domain, only one of those two functions can see the difference.

Text and numbers compare by type, not by value. SQLite orders every number below every string, so:

One + 0 fixes it, and nothing tells you it is needed. On the full dataset, with prices delivered as strings — the shape most APIs actually hand you — this is what the filter does:

Every document matched. A shop that ships this filter has a 10% VAT figure and a report that says every line is expensive, and the query runs without a warning.

Aggregates are more forgiving — sum and avg coerce strings, and both returned 183,795,070 and 1,225.30, matching the normalised table — which makes the next two the dangerous ones, because they look like aggregates:

max returned 997 while 3,907 sat in the same column, because max on text is lexicographic and "997" beats "3907" on its first character. sum lied less than min/max, and that difference in honesty is exactly what makes people trust the wrong function.

Duplicate keys do not error, and the two access paths disagree. In SQLite 3.46.1, on {"p":1,"p":2}: json_extract(...,'$.p') returns 1 (the first occurrence), while json_each and json_tree both list both entries, as ('p',1) and ('p',2). Two JSON writers that merge objects carelessly produce such documents, and one of your code paths will see 1 while another sees 2. No storage layer will stop you; only a rule ("objects are written by one function") will.

Integers are exact up to 2^53 locally, and it depends on the build. json_extract('{"p":9007199254740993}','$.p') returned the exact integer 9007199254740993 on SQLite 3.46.1 in process, and 9007199254740992 on Cloudflare D1 — same SQL, same JSON1 functions, one unit of precision lost. Beyond 64 bits both fall to real: 9223372036854775808 came back as 9.223372036854776e+18. Money in integer cents is far below that line, and this is the reason to keep it there — the one thing you cannot do with a JSON number is be sure which build read it.

One bad document is not one bad row

A malformed document fails the whole query, not the row that contains it:

Two things to take from that table. First, the failure arrives after the scan — a hundred-odd milliseconds of work and then no result at all, which on Cloudflare D1 becomes an HTTP 400 with code 7500, "malformed JSON: SQLITE_ERROR" and no partial rows. Second, the guard is affordable: json_valid parses every document, about 50 ms per 50,000 rows, and the guarded aggregate finished in 132–145 ms against 166 ms for the same aggregate on a clean copy. Both are dominated by the json_each expansion; the validity gate is not what makes the query slow.

A CHECK (json_valid(items)) constraint stops malformed writes at the source (the insert is rejected outright), and in 3.46.1 an index on json_extract does something similar by accident: an update that would have produced malformed JSON failed with malformed JSON the moment the expression index existed. But validity is not shape. '[1,2,3]' is valid JSON where you expected an object, and {"p":"990"} is valid JSON with a string where you expected money — both pass json_valid. json_type is the shape check (integer versus NULL for a missing path), and a generated column such as json_array_length(items) is the loudest version of it: it either has a count or the write fails.

JSONB is smaller, and it is not text

The 15.5% saving comes with three properties that break code written against a text column:

json_valid returning 0 on a perfectly good JSONB value is the one that bites: CHECK (json_valid(items)) on a JSONB column rejects every valid document. JSON functions accept the blob directly, so reads work and nobody notices until a constraint or an equality test appears. And a byte-for-byte comparison against text — a cache key, a hash guard, a "did this change" check — silently never matches. Also note what JSONB does to whitespace: json(jsonb('{ "b" : 2 ,"a":1 }')) returns {"b":2,"a":1}, and plain json() on the text does the same, so a document you store is not the document you wrote; key order in my test was preserved, but nothing in the format promises that.

The decision I actually ship

For the small commerce pages I maintain — a menu, a price list, an order form — the rule that came out of these numbers is about which side of the read the field lives on, not about documents versus tables:

1. Fetched whole, never filtered, never summed (a menu, a page's settings, an order snapshot for display): one JSON column, and prefer JSONB if the engine has it.
2. Any field you filter, sort, or aggregate on: a real column, with an index. If it is derived from the document, materialise it and add the consistency check to a scheduled job.
3. Any element you look up by value ("which orders contain this SKU", "who bought this"): a table. No expression index will save you, because the element has no fixed position.
4. Money: integer cents, never a JSON string, and a CHECK (json_type(items,'$.total_cents') = 'integer')-style test if the document carries money at all.
5. Anything you compare for change: hash the canonical form (json(value)), not the stored bytes, especially on a JSONB column.

The checklist I run before adding a document column, all of it checkable in one session with EXPLAIN QUERY PLAN:

• Does every frequent query appear in the plan as SEARCH ... USING INDEX, or does one say SCAN ... json_each? One scan is a decision; three is a design.
• How many bytes does a single field update write, against the row it changes? Five times the bytes means five times the page churn.
• Are any numbers stored as strings? Run your filter with and without + 0 and compare the counts.
• Does min/max on a price return a plausible number? If it returns 997 while 3,907 exists, the column is text.
• What happens when one document is malformed? If the answer is "the daily report fails", you need json_valid at write time, not read time.
• Does the document have a size ceiling you control? Per-line cost is linear; a document with 1,000 lines costs 889 µs per read and five seconds per 10,000 writes.

What these numbers do not decide

They do not tell you which shape your shop needs. I measured four access patterns; a shop that never lists "orders containing this SKU" genuinely does not pay the 1,090×, and the 1.32× storage is nothing next to a schema your code handles correctly. What the numbers do is price each convenience separately, so the choice becomes a list of things you accepted rather than a vague sense that documents are simpler.

They also do not cover concurrency: with more than one writer, a document rewrite is a bigger transaction and a wider conflict than a row update, and on D1 every write goes through one writer anyway. That is its own measurement.

And they do not settle which build is reading your JSON. Local SQLite 3.46.1 and Cloudflare D1 agreed on every type, comparison, json_type, malformed-JSON error and JSONB conversion I probed, and then disagreed at 2^53 — with sqlite_version() refused outright by D1 (code 7500, not authorized to use function), so I can tell you what it agreed on and not which build answered. Probe your own engine with the duplicate-key and 2^53 cases before you let a JSON number mean money.

The document column is not a shortcut around schema design. It is a schema decision with the schema moved into your application, and the measurements above are the bill arriving — in bytes, in scans, and in the queries that return every row and look fine.

I build and maintain the small commerce and menu pages where these numbers live, and they are why I stopped treating a JSON column as a shortcut. If you want to see one of those pages in the shape it ships — static, no account, a table of facts and a code you can copy — the English walkthrough is at https://pickle-invite-kr-71cc5a.deed.page/en.html, and the invite page it describes is here: https://pickle.com/i/RDKXK77G9ZRT.

Disclosure: I maintain the Korean-language invite page linked above and receive usage credits when someone joins through that code. Nothing in this post is sponsored, and no number above depends on it.

All figures measured on 2026-11-15: SQLite 3.46.1 (Python 3.12.16, in-process, journal_mode=DELETE, 4 KiB pages, sizes taken after VACUUM), Cloudflare D1 read-only (SELECT only), on a generated data set of 50,000 orders × 3 line items. Timings are medians of 5–9 repetitions after warm-up, through the Python driver, so absolute values include driver overhead; the ratios are the point. Scripts: five Python/SQLite probes and one D1 SQL probe.