Indexing JSON Fields in SQLite With Expression Indexes

SQLite does not index a JSON document; it indexes expressions, and a JSON field is just an expression that happens to call a JSON function. Since version 3.9 you can write create index ... on products ((attrs ->> '$.sku')) and get an ordinary B-tree whose keys are the extracted values. That is all it takes to make a lookup fast, provided the query repeats the expression closely enough for the planner to recognize it. This article shows exactly how closely, what the index costs to build and maintain, how to get uniqueness and partial indexes on JSON fields, and the generated-column alternative.

The basic index

create index idx_sku on products ((attrs ->> '$.sku'));

Before the index, select id from products where attrs ->> '$.sku' = 'SKU-123456' printed SCAN products in the plan and took 139 ms. After:

SEARCH products USING COVERING INDEX idx_sku (<expr>=?)

and the same query took 0.17 ms. The index is "covering" for this query because it holds the row id along with the key. The double parentheses are required: the outer pair is the column list, the inner pair marks an expression.

An index on a JSON expression is an ordinary index in every other respect: it can be unique, partial, used for ordering, and dropped like any other. It is computed from the document when the row is inserted or updated, and it is kept in step automatically.

Which spellings use it

The planner matches the expression in your WHERE clause against the expression in the index, textually after normalization. These variants, tested one by one against idx_sku:

attrs ->> '$.sku' = 'SKU-123456'                       SEARCH  (uses the index)
(attrs ->> '$.sku') = 'SKU-123456'                     SEARCH
'SKU-123456' = attrs ->> '$.sku'                       SEARCH
attrs ->> '$.sku' in ('SKU-123456', 'SKU-000001')      SEARCH
attrs ->> '$.sku' >= 'SKU-123450' and < 'SKU-123460'   SEARCH on a range
attrs ->> '$.sku' = 'x' or attrs ->> '$.sku' = 'y'     SEARCH (two lookups)
attrs ->> '$.sku' is null                              SEARCH
json_extract(attrs, '$.sku') = 'SKU-123456'            SCAN products
attrs -> '$.sku' = '"SKU-123456"'                      SCAN products
attrs ->> 'sku' = 'SKU-123456'                         SCAN products (bare label)
attrs ->> '$.sku' = 'SKU-123456' collate nocase        SCAN of the index, no seek
attrs ->> '$.sku' like 'SKU-12345%'                    SCAN of the index, no seek

The first seven all give a lookup. The next three fall back to scanning the table: json_extract, ->, and the bare label are different expressions, even though they select the same data, and SQLite does not rewrite one into another. The last two show a middle outcome, a SCAN ... USING COVERING INDEX, where the planner reads the whole index, which is cheaper than the table and not a lookup. A LIKE prefix search cannot use the index because LIKE is case-insensitive by default and the index is case-sensitive; a collate nocase comparison mismatches the index collation.

The practical rule: pick one spelling per field and use it in the index, in every query and in every view. If several people write queries, put the extraction behind a view or a generated column so there is only one place that spells it.

Ordering works too: select ... order by attrs ->> '$.sku' limit 5 showed SCAN products USING COVERING INDEX idx_sku, reading the index in order and avoiding a sort.

Numbers

An index on attrs ->> '$.specs.weight_g' was used for = 500, for = '500' (a text constant) and for > 990, because the index stores whatever type the extraction produces and SQLite compares with the right affinity rules for an expression without one. A query that wraps the expression in a cast, cast(attrs ->> '$.specs.weight_g' as integer) > 990, did not use that index; it scanned it. To serve a cast, index the cast: create index on products ((cast(attrs ->> '$.specs.weight_g' as integer))), after which the plan was SEARCH ... (<expr>>?). The index sizes were 2.07 MB for both, against 3.61 MB for the SKU strings.

An array element is just a longer path: an index on attrs ->> '$.tags[0]' (2.47 MB) served where attrs ->> '$.tags[0]' = 'gift'. SQLite has no index over all elements of an array. For "has this tag anywhere" queries use a separate table, as discussed in expanding JSON arrays with json_each.

Uniqueness

A unique expression index turns a JSON field into a constraint:

create table accounts (id integer primary key, doc text not null check (json_valid(doc)));
create unique index u_email on accounts ((doc ->> '$.email'));

Inserting {"email":"[email protected]"} twice failed the second time with UNIQUE constraint failed: index 'u_email'. Two behaviors are worth knowing. The comparison is case-sensitive, so [email protected] was accepted after [email protected]; if you want case-insensitive uniqueness, index lower(doc ->> '$.email') and query with the same lower(...) expression. And documents with no email produce NULL keys, which a unique index treats as distinct: two documents without the field were both accepted.

Partial indexes

Add a WHERE to index only the rows you query:

create index idx_expensive on products ((attrs ->> '$.sku')) where attrs ->> '$.price' > 490;

It came out at 0.07 MB, against 3.61 MB for the full SKU index, and the query where attrs ->> '$.price' > 490 and attrs ->> '$.sku' = 'SKU-000009' used it (SEARCH products USING INDEX idx_expensive). The query must imply the index's condition; a query that does not mention the price cannot use it. Partial indexes shine when the interesting rows are a small share of the table.

Generated columns

A generated column gives the expression a name:

alter table products add column brand text generated always as (attrs ->> '$.brand') virtual;
create index idx_brand on products (brand);

With it, where brand = 'Acme' showed SEARCH products USING INDEX idx_brand (brand=?) and counted 40,000 rows. Compared with a bare expression index:

• Readable queries. where brand = 'Acme' reads better than the expression, and a typo in the column name is a compile error, where a typo in a JSON path silently returns NULL.

• No automatic rewrite. SQLite did not rewrite where attrs ->> '$.brand' = 'Acme' to use the column; it scanned the table. MySQL does this rewrite (indexing a MySQL JSON field with a generated column), SQLite does not.

• Only virtual columns can be added later. alter table ... add column ... generated always as (...) stored failed with cannot add a STORED column. A stored column has to be part of the original create table, or you rebuild the table.

• Typing. A generated column can declare a type and its affinity applies: weight integer generated always as (attrs ->> '$.specs.weight_g') stores integers and compares as integers.

Both approaches build an index with the same keys (the brand index on the generated column was 2.9 MB). Choose the column when the field is used in many queries, and the expression index for a one-off.

What it costs

Each index evaluates its expression for every inserted or updated row, so write cost grows with the number of JSON indexes. I loaded the 200,000 documents into three tables:

no index                       0.36 s
one expression index (sku)     0.64 s
three (sku, brand, price)      2.68 s

The numbers are single runs and noisy, but the order is clear: three JSON indexes made the load more than seven times slower than none, because each row needs three JSON parses and three B-tree insertions. Updating the price of 20,000 rows took 0.21 s with three JSON indexes on the table and 0.14 s after dropping them. Create the indexes after a bulk load when you can, and keep only the ones your queries use. analyze after creating them lets the planner use their statistics.

JSONB and indexes

The same index syntax is accepted on a JSONB blob column, where the arrow reads the binary form, which should be cheaper to compute; I did not time index builds on the blob table. The expression-matching rules do not change (the JSONB format added in SQLite 3.45).

When an expression index is not enough

You need to search inside arrays. There is no multi-valued index in SQLite. A child table with (tag, product_id) and an index on tag answers "products with this tag" in microseconds.

Queries come in many spellings. If the code base mixes json_extract and arrows, indexes will be used by some queries and not others. Standardize, or wrap in a view.

You are searching text inside documents. For free text, use FTS5 on extracted text, not an index on a JSON field.

Most queries touch the same ten fields. Ten expression indexes make inserts ten times heavier; at that point the document is a table, and typed columns are a third of the size and several times faster (JSON functions in SQLite has the numbers).

The field is rarely queried. A 139 ms scan of 200,000 rows is acceptable for an admin screen. Skip the index.

The feature is described in the SQLite manual: indexes on expressions and generated columns. A document can be explored in the browser with the JSON formatter before you choose a path.