Indexing JSON Paths, Generated Columns, Validation, and Hybrid Designs

Learning outcomes

JSON flexibility does not remove indexing requirements. If one document path becomes a frequent predicate, SQLite can expose that deterministic scalar as a generated column or expression index. The design should still ask whether the property has become important enough to deserve an ordinary relational column.

Validation is the first index prerequisite

An indexable expression is useful only if the input contract is predictable. For canonical text JSON that is mandatory, a compact schema pattern is TEXT NOT NULL CHECK(json_valid(metadata)). If SQL NULL is allowed, make that choice explicit with CHECK(metadata IS NULL OR json_valid(metadata)).

CREATE TABLE valid_required (  id INTEGER PRIMARY KEY,  metadata TEXT NOT NULL CHECK(json_valid(metadata)));CREATE TABLE valid_optional (  id INTEGER PRIMARY KEY,  metadata TEXT CHECK(metadata IS NULL OR json_valid(metadata)));

If the application intentionally accepts JSON5 input, decide whether to canonicalize it with json() before storage. A path index should not be built on an undefined mix of conventions.

Generated columns give an important path a relational name

Generated columns are computed from same-row deterministic expressions. SQLite's JSON functions are deterministic, so a scalar path such as firmware version is a good candidate. A VIRTUAL generated column stores no separate column payload and computes on read; it can still participate in an index.

CREATE TABLE device_profile_indexed (  device_id INTEGER PRIMARY KEY            REFERENCES device(device_id) ON DELETE CASCADE,  metadata TEXT NOT NULL CHECK(json_valid(metadata)),  firmware_version TEXT    GENERATED ALWAYS AS (json_extract(metadata,'$.firmware.version')) VIRTUAL,  calibration_due TEXT    GENERATED ALWAYS AS (json_extract(metadata,'$.calibration.due')) VIRTUAL);INSERT INTO device_profile_indexed(device_id, metadata)SELECT device_id, metadata FROM device_profile;

Generated columns require SQLite 3.31.0+. Use PRAGMA table_xinfo(device_profile_indexed), not only table_info, when you need to see generated columns in introspection.

Measure before the index

Start with a plan rather than assuming JSON extraction is the bottleneck. On a tiny three-row course table SQLite may reasonably scan regardless of an available index, so the lab uses a larger disposable table when a visible plan contrast is needed.

EXPLAIN QUERY PLANSELECT device_idFROM device_profile_indexedWHERE firmware_version='3.7.2';

Before an index exists, expect a SCAN device_profile_indexed style plan. Exact EXPLAIN QUERY PLAN text is diagnostic output and can vary by SQLite release.

Index the generated value and prove SEARCH

CREATE INDEX idx_device_profile_firmwareON device_profile_indexed(firmware_version);EXPLAIN QUERY PLANSELECT device_idFROM device_profile_indexedWHERE firmware_version='3.7.2';

With a sufficiently meaningful dataset, the plan can change to SEARCH ... USING INDEX idx_device_profile_firmware. That evidence means SQLite can navigate the B-tree by the extracted value instead of re-evaluating the path for every row visited.

Expression indexes remove the named generated column

SQLite can also index an expression directly. The indexed expression may reference only columns of the indexed table and must use deterministic functions. Subqueries and nondeterministic functions are not allowed.

CREATE INDEX idx_device_profile_protocol_exprON device_profile_indexed(  json_extract(metadata,'$.network.protocol'));EXPLAIN QUERY PLANSELECT device_idFROM device_profile_indexedWHERE json_extract(metadata,'$.network.protocol')='modbus';

This can be compact when the path is purely an access optimization. A generated column is often easier to reuse consistently across queries, reports, and application mappings.

Apparently equivalent syntax may not match the expression index

SQLite's expression-index matching is syntactic rather than an algebra system. The planner tolerates minor whitespace differences, but it generally expects the query expression to match the indexed expression. The ->> operator and json_extract() can return equivalent scalar values while still being different expressions for index matching.

EXPLAIN QUERY PLANSELECT device_idFROM device_profile_indexedWHERE metadata ->> '$.network.protocol' = 'modbus';-- Compare with the expression that exactly matches the index:EXPLAIN QUERY PLANSELECT device_idFROM device_profile_indexedWHERE json_extract(metadata,'$.network.protocol') = 'modbus';

The first query may scan while the second can search using idx_device_profile_protocol_expr. Verify the actual plan on your version and dataset instead of assuming semantic equivalence guarantees index reuse.

Large lab: make the plan difference visible

CREATE TABLE profile_probe (  id INTEGER PRIMARY KEY,  metadata TEXT NOT NULL CHECK(json_valid(metadata)),  protocol TEXT GENERATED ALWAYS AS (    json_extract(metadata,'$.network.protocol')  ) VIRTUAL);WITH RECURSIVE seq(n) AS (  VALUES(1) UNION ALL SELECT n+1 FROM seq WHERE n < 30000)INSERT INTO profile_probe(id, metadata)SELECT n,       json_object(         'network',json_object('protocol',           CASE WHEN n%100=0 THEN 'modbus' ELSE 'mqtt' END),         'firmware',json_object('version',printf('3.%d.%d',n%8,n%20))       )FROM seq;EXPLAIN QUERY PLANSELECT count(*) FROM profile_probe WHERE protocol='modbus';CREATE INDEX idx_profile_probe_protocol ON profile_probe(protocol);EXPLAIN QUERY PLANSELECT count(*) FROM profile_probe WHERE protocol='modbus';

The expected qualitative transition is from SCAN to SEARCH. The exact row estimates/details are version-specific diagnostics.

Promote a JSON property when its meaning outgrows metadata

An index can make a JSON path fast, but it does not automatically make the hidden schema clear. If firmware_version becomes mandatory, participates in release eligibility, requires a foreign key to an approved-version table, or is updated independently as a core business fact, an ordinary column may express the contract better.

Schema evolution: promotion is a migration, not a copy-paste

When promoting a path, define source-of-truth ownership first. A safe migration can add the relational column, backfill it from validated JSON, verify discrepancies, update writers to maintain only the new authoritative representation, and finally remove or stop reading the duplicated JSON key. Chapter 17 will formalize migration sequencing and compatibility.

SELECT device_id,       json_type(metadata,'$.firmware.version') AS json_type,       json_extract(metadata,'$.firmware.version') AS candidate_valueFROM device_profileWHERE COALESCE(json_type(metadata,'$.firmware.version'),'missing') <> 'text';

Do not backfill first and ask data-quality questions later.

Lab: direct JSON predicate versus indexed generated column

-- Direct path expression without a matching expression indexEXPLAIN QUERY PLANSELECT count(*)FROM profile_probeWHERE json_extract(metadata,'$.network.protocol')='modbus';-- Named generated column with its indexEXPLAIN QUERY PLANSELECT count(*)FROM profile_probeWHERE protocol='modbus';

Record both plans and verify the result count is identical. Performance claims are valid only when semantic results match.

Production hybrid-design checklist

1. Identify which facts are authoritative relational columns and forbid duplicate truth inside JSON.
2. Choose a JSON contract: canonical text, JSON5 input canonicalized to text, or intentionally SQLite-specific JSONB.
3. Validate at the database boundary when malformed input would violate the data contract.
4. Document member names, expected JSON types, null/missing semantics, and version evolution.
5. Measure repeated JSON traversal/extraction before adding indexes.
6. Use generated/expression indexes only for deterministic row-local expressions and verify plans.
7. Promote paths that become critical invariants, join keys, high-value filters, or independently managed entities.
8. Keep interoperability requirements visible before adopting JSONB.
9. Test with the SQLite library actually bundled by each application, not only a developer CLI.

Verification checkpoint

Chapter 13 complete: JSON without abandoning relational discipline

You can now model JSON deliberately, navigate it with predictable types, traverse nested arrays/objects relationally, choose text versus JSONB based on measured requirements, and make selected paths indexable. Chapter 14 builds on this “table-like interface over specialized behavior” idea with virtual tables, FTS5, R-Tree, and extension safety.

Authoritative references

• SQLite JSON Functions and Operators
• SQLite Generated Columns
• SQLite Indexes on Expressions
• SQLite EXPLAIN QUERY PLAN
• SQLite 3.53.4 release history