JSON Support — json_extract, json_each, JSONB

SQLite as a document store, when you need it

SQLite has shipped the JSON1 extension by default since 3.38. You can store JSON in a TEXT column and query into it with SQL functions:

json_extract(col, '$.path') — pull a value out by JSON path.
json_each(col) / json_tree(col) — table-valued functions to iterate JSON elements.
json_set(col, '$.path', value) / json_remove / json_replace — modify.
json_array / json_object / json_group_array — build JSON.

Since 3.45 (Jan 2024), SQLite also supports JSONB — a binary representation that's faster to parse on every access. Store as JSONB when the column is large or queried often.

Tip: Index expressions on extracted JSON paths to make queries fast: CREATE INDEX idx_meta_status ON events(json_extract(meta, '$.status')). Then WHERE json_extract(meta, '$.status') = 'pending' uses the index.

Code

JSON1 patterns·sql

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  ts TEXT NOT NULL DEFAULT (datetime('now')),
  meta TEXT NOT NULL                       -- JSON
) STRICT;

INSERT INTO events(meta) VALUES
  (json_object('status', 'pending', 'tags', json_array('a','b'))),
  (json_object('status', 'done',    'tags', json_array('a')));

-- Extract a value
SELECT id, json_extract(meta, '$.status') AS status FROM events;

-- Filter by JSON value
SELECT * FROM events WHERE json_extract(meta, '$.status') = 'pending';

-- Iterate array elements
SELECT e.id, value AS tag
FROM   events e, json_each(e.meta, '$.tags');

Index a JSON path for fast filtering·sql

CREATE INDEX idx_events_status
  ON events(json_extract(meta, '$.status'));

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE json_extract(meta, '$.status') = 'pending';
-- USING INDEX idx_events_status

Exercise

Build an events table that stores arbitrary JSON metadata. Insert 100k rows with varying shapes. Write three queries that pull values out of the JSON, then add an expression index on the most-queried path and confirm the EXPLAIN plan changed. Then store the same data in a JSONB column (3.45+) and compare query speed.

JSON Support — json_extract, json_each, JSONB

SQLite as a document store, when you need it

Code

External links

Exercise

Progress

Comments 0