sqlANYsqlitejson_extract
sql
SELECT json_extract(payload, '$.type') AS event_type FROM events;A core pattern for event logs and flexible payload columns.
SQLite JSON Functions Cheatsheet
json_extract, json_set, json_each, JSON operators, generated columns, and practical JSON query/update patterns in SQLite.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Read JSON values
Extract fields and project JSON into relational queries.
sqlANYsqlitejson operatorsarrow
sql
SELECT payload -> '$.type' AS raw_json,
payload ->> '$.type' AS text_value
FROM events;`->` returns JSON text while `->>` returns a scalar SQL value when appropriate.
sqlANYsqlitejson_valid
sql
SELECT json_valid(payload) FROM events;Useful before imports, migrations, or backfilling generated columns.
sqlANYsqlitejson_type
sql
SELECT json_type(payload, '$.user') FROM events;Helpful when cleaning inconsistent payloads from multiple producers.
Update and expand JSON
Patch documents and turn arrays into rows.
sqlANYsqlitejson_setupdate
sql
UPDATE settings
SET config = json_set(config, '$.theme', 'dark')
WHERE user_id = 1;A practical way to update JSON without replacing the whole document in app code.
sqlANYsqlitejson_remove
sql
UPDATE settings
SET config = json_remove(config, '$.deprecated_flag')
WHERE user_id = 1;Useful during schema cleanup or feature-flag retirement.
sqlANYsqlitejson_eacharray
sql
SELECT value AS tag
FROM json_each('["sqlite", "json", "fts"]');`json_each` is a table-valued function and belongs in the FROM clause.
sqlANYsqlitejson_eachjoin
sql
SELECT a.id, je.value AS role
FROM accounts a,
json_each(a.roles_json) AS je
WHERE je.value = 'admin';Great for analytics and migrations, though highly queried JSON fields are often better normalized or indexed via generated columns.
More in SQLite
SQLite Triggers, Views, and Generated Columns Cheatsheet
CREATE VIEW, CREATE TRIGGER, audit logging, derived values, generated columns, and automation patterns in SQLite.
SQLite Full-Text Search (FTS5) Cheatsheet
FTS5 virtual tables, MATCH queries, prefix search, phrase search, bm25 ranking, snippets, and highlighting patterns for SQLite search features.
SQLite PRAGMA and Administration Cheatsheet
High-signal PRAGMA commands for foreign keys, cache, synchronous settings, page size, temp storage, health checks, and general SQLite administration.
SQLite Import, Export, and Backup Cheatsheet
CSV import/export, SQL dumps, VACUUM INTO, backup copies, and repeatable data movement patterns for SQLite databases.
SQLite Transactions, WAL, and Locking Cheatsheet
BEGIN modes, COMMIT and ROLLBACK, savepoints, WAL mode, busy timeouts, and concurrency patterns for SQLite applications.
SQLite Indexes and Query Planner Cheatsheet
CREATE INDEX patterns, covering and partial indexes, EXPLAIN QUERY PLAN, ANALYZE, and practical tuning tips for SQLite queries.