SQLite Index on JSON Expression
SQLite Index on JSON Expression is a SQL statement in the SQLite Specific category. SQLite 3.9+: indexes on JSON path expressions for efficient queries on JSON data. The syntax is CREATE INDEX idx_json_price ON items (json_extract(data, '$.price'));. It returns jSON-indexed query. A typical example: CREATE TABLE items (data TEXT); CREATE INDEX idx_items_price ON items (json_extract(data, '$.price')); INSERT INTO items VALUES ('{"name": "Widget", "price": 9.99}'); INSERT INTO items VALUES ('{"name": "Gadget", "price": 19.99}'); -- Uses the JSON index: SELECT json_extract(data, '$.name') AS name FROM items WHERE json_extract(data, '$.price') > 10; -- Index lookup on extracted price value -- WITHOUT the index, every row must be parsed from JSON -- WITH the index, searches are B-tree… A close relative is AUTOINCREMENT (SQLite), which sQLite: unlike AUTO_INCREMENT in MySQL, only INTEGER PRIMARY KEY columns auto-increment. AUTOINCREMENT guarantees unique increasing IDs. A close relative is ROWID / _ROWID_, which sQLite: every row has an implicit 64-bit ROWID. INTEGER PRIMARY KEY becomes an alias for ROWID.