TopolearnThese modern features let SQLite handle flexible data and idempotent writes in single statements.
SQLite stores JSON as ordinary text (or, since 3.45, in a binary JSONB blob format via the jsonb_* functions) and ships a rich set of JSON functions.
doc -> '$.address'returns JSON text;doc ->> '$.address.city'returns an SQL value (text, integer, real or NULL). Both arrived in 3.38;json_extract()works everywhere.json_each(doc, '$.tags')is a table-valued function: one row per array element, so you can join and filter on it.json_set,json_insert,json_replace,json_removereturn modified documents;json_objectandjson_group_arraybuild JSON from rows.- Index a JSON field with an expression index:
CREATE INDEX ix ON t (doc ->> '$.sku'), used when queries contain the same expression.
Upsert (3.24+) uses PostgreSQL's syntax: INSERT ... ON CONFLICT (col) DO UPDATE SET qty = qty + excluded.qty, or DO NOTHING. The conflict target must match a unique index or primary key. Older SQLite code uses INSERT OR REPLACE, which deletes and re-inserts the row — triggering deletes, cascades and new rowids — so prefer ON CONFLICT.
RETURNING (3.35+) makes INSERT, UPDATE and DELETE return the affected rows, including generated ids and defaults, saving a second query.
Example
import sqlite3, json
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE product (sku TEXT PRIMARY KEY, qty INTEGER, doc TEXT)")
con.execute("CREATE INDEX product_brand ON product (doc ->> '$.brand')")
con.execute("INSERT INTO product VALUES (?, ?, ?)",
("A1", 5, json.dumps({"brand": "Acme", "tags": ["sale", "new"]})))
print(con.execute("SELECT doc -> '$.tags', doc ->> '$.brand' FROM product").fetchone())
# ('["sale","new"]', 'Acme')
print(con.execute('''SELECT p.sku, t.value FROM product p, json_each(p.doc, '$.tags') t
WHERE t.value = 'sale' ''').fetchall()) # [('A1', 'sale')]
row = con.execute('''INSERT INTO product (sku, qty) VALUES ('A1', 3)
ON CONFLICT (sku) DO UPDATE SET qty = qty + excluded.qty
RETURNING sku, qty''').fetchone()
print(row) # ('A1', 8)Remember
- -> returns JSON, ->> returns an SQL value
- json_each turns JSON arrays into rows
- Prefer ON CONFLICT ... DO UPDATE over INSERT OR REPLACE; RETURNING saves a query
Watch out for
- Using INSERT OR REPLACE as an upsert and losing columns not included in the insert.