SQLite § json

JSON

JSON is the conventional interchange format for HTTP APIs, configuration files, and event-stream payloads, and SQLite has a substantial library of functions for storing, querying, and transforming JSON values. The original implementation, JSON1, was added in version 3.9 (2015) and stores JSON as ordinary TEXT. The binary form JSONB, added in version 3.45 (2024), stores JSON in a compact internal representation that elides parsing on read; it is the recommended form for new applications where the JSON is not consumed externally as raw text.

This page covers the JSON1 function library, the path syntax, the -> and ->> operators, the json_each and json_tree table-valued functions, the JSONB representation and its trade-offs, expression indexes for indexing into JSON columns, and the recommendation about when to normalise JSON into proper columns.

Storing JSON

JSON values may be stored as TEXT in any column whose affinity accepts text. The recommended pattern is a column declared TEXT with a CHECK (json_valid(col)) constraint that rejects malformed JSON at insert time:

CREATE TABLE event (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL CHECK (json_valid(payload))
) STRICT;

INSERT INTO event (payload) VALUES ('{"user_id":1,"action":"login"}');
INSERT INTO event (payload) VALUES ('{not json}');
-- Error: CHECK constraint failed: event

The json_valid check is a constant-time parse and adds negligible cost to the insert. The constraint is the canonical way to ensure that the payload column always contains parseable JSON.

For applications where the JSON is large and is not consumed as text outside SQLite, JSONB is the storage form to prefer:

CREATE TABLE event (
    id      INTEGER PRIMARY KEY,
    payload BLOB NOT NULL CHECK (jsonb_valid(payload))
) STRICT;

INSERT INTO event (payload) VALUES (jsonb('{"user_id":1,"action":"login"}'));

The jsonb() function produces the binary representation; the storage is a BLOB, the validation function is jsonb_valid. Reads through the JSON functions are roughly twice as fast as the equivalent JSON1 operations on text, because the parse is amortised at write time.

Path syntax

JSON values are addressed by paths. A path is a string beginning with $ (the root) followed by a sequence of step expressions:

Path	Selects
`$`	The root value.
`$.key`	The value of `key` in an object.
`$.key.subkey`	The value of `subkey` in `key`’s object.
`$[0]`	The first element of an array (0-indexed).
`$[0].key`	The `key` of the first element of an array.
`$.key[2]`	The third element of `key`’s array.
`$.**` (since 3.46)	Wildcard, matches any sub-path.

A key that contains a non-identifier character is double-quoted: $."first name".

Paths are accepted by every JSON function that takes a path argument; they appear as quoted strings in SQL.

Extracting values

json_extract(json, path) returns the value at the path. The return type depends on the JSON value: a JSON number returns as INTEGER or REAL, a JSON string returns as TEXT, an object or array returns as a JSON-encoded text.

SELECT json_extract('{"a":1,"b":[2,3]}', '$.a');         -- 1
SELECT json_extract('{"a":1,"b":[2,3]}', '$.b');         -- [2,3]
SELECT json_extract('{"a":1,"b":[2,3]}', '$.b[0]');      -- 2
SELECT json_extract('{"a":"x"}', '$.a');                 -- 'x'

Multiple paths may be supplied; the result is a JSON array:

SELECT json_extract('{"a":1,"b":2,"c":3}', '$.a', '$.c');
-- '[1,3]'

The -> and ->> operators

The two operators introduced in 3.38 are syntactic sugar for json_extract, with one important distinction:

Operator	Returns
`->`	The value as JSON (a JSON-encoded fragment).
`->>`	The value as a SQLite scalar (text or numeric).

SELECT '{"a":"x"}' -> '$.a';      -- '"x"'   (JSON-encoded)
SELECT '{"a":"x"}' ->> '$.a';     --  x      (SQLite TEXT)

SELECT '{"a":[1,2,3]}' -> '$.a';      -- '[1,2,3]'
SELECT '{"a":[1,2,3]}' ->> '$.a';     -- '[1,2,3]' (still a JSON-encoded array; ->> only affects scalars)

The path argument can be a bare key for top-level access:

SELECT '{"name":"Ada"}' ->> 'name';   -- 'Ada'
SELECT '[10,20,30]' ->> 1;             -- '20'

The ->> form is the conventional choice when the extracted value is then used in arithmetic, comparison, or string operations:

SELECT id FROM event WHERE payload ->> '$.user_id' = 42;
SELECT id, payload ->> '$.action' AS action FROM event;

Constructing JSON

Function	Returns
`json(text)`	Parses and re-emits, normalising whitespace.
`jsonb(text)`	Produces a JSONB BLOB.
`json_array(a, b, …)`	A JSON array.
`json_object(k1, v1, k2, v2, …)`	A JSON object.
`json_quote(s)`	A JSON string literal.
`json_group_array(col)`	An aggregate: a JSON array of column values.
`json_group_object(key, val)`	An aggregate: a JSON object.

SELECT json_array(1, 2, 'three', NULL);
-- '[1,2,"three",null]'

SELECT json_object('id', 1, 'name', 'Ada', 'tags', json_array('admin','user'));
-- '{"id":1,"name":"Ada","tags":["admin","user"]}'

SELECT json_group_array(name) FROM customer;
-- '["Ada","Donald","Grace"]'

SELECT json_group_object(id, name) FROM customer;
-- '{"1":"Ada","2":"Donald","3":"Grace"}'

The aggregates are particularly useful for shaping report output: a single SQL query can produce a fully-formed JSON document, which the application then emits without further transformation.

Modifying JSON

Five functions modify a JSON value, producing a new value (the original is unchanged; there is no in-place mutation):

Function	Effect
`json_set(json, path, value, …)`	Replace or insert.
`json_replace(json, path, value, …)`	Replace if path exists; do nothing if absent.
`json_insert(json, path, value, …)`	Insert if path absent; do nothing if present.
`json_remove(json, path, …)`	Remove paths.
`json_patch(json, patch)`	RFC 7396 JSON Merge Patch.

UPDATE event
SET payload = json_set(payload, '$.processed_at', strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
WHERE id = ?;

UPDATE event
SET payload = json_remove(payload, '$.internal')
WHERE json_extract(payload, '$.internal') IS NOT NULL;

json_patch is convenient for shallow merges:

UPDATE user
SET settings = json_patch(settings, '{"theme":"dark","notifications":null}')
WHERE id = ?;

The patch sets theme to 'dark' and removes notifications (the null in a merge patch indicates removal).

Iterating JSON: json_each and json_tree

The two table-valued functions expand a JSON value into rows, permitting JSON to be queried with the full SQL surface.

json_each(json) returns one row per top-level element:

SELECT key, value, type FROM json_each('{"a":1,"b":2,"c":3}');
-- a|1|integer
-- b|2|integer
-- c|3|integer

SELECT key, value FROM json_each('[10,20,30]');
-- 0|10
-- 1|20
-- 2|30

json_tree(json) returns one row per element at any depth:

SELECT path, key, value, type
FROM json_tree('{"a":1,"b":[2,3],"c":{"d":4}}');
-- $    |    |{"a":1,"b":[2,3],"c":{"d":4}}|object
-- $.a  |a   |1                            |integer
-- $.b  |b   |[2,3]                        |array
-- $.b[0]|0  |2                            |integer
-- $.b[1]|1  |3                            |integer
-- $.c  |c   |{"d":4}                      |object
-- $.c.d|d   |4                            |integer

The functions are the canonical mechanism for querying inside JSON: a query that finds every event whose JSON payload contains a particular tag, for instance, would use json_each over the tags array.

-- Find every event with the 'urgent' tag
SELECT DISTINCT e.id
FROM event AS e, json_each(e.payload, '$.tags') AS j
WHERE j.value = 'urgent';

The , json_each(…) is a correlated table-valued function: the e.payload argument references the outer row, so json_each is evaluated separately for each event.

Indexing JSON

A column containing JSON is opaque to the engine — the engine sees only TEXT (or BLOB for JSONB) and cannot use a plain B-tree to find rows with a particular extracted value. Expression indexes (see Indexes and query planning) bridge the gap:

CREATE INDEX ix_event_user
    ON event (json_extract(payload, '$.user_id'));

-- The query uses the index:
SELECT id FROM event WHERE json_extract(payload, '$.user_id') = 42;

The expression in the index must match the expression in the query exactly; a query that uses payload ->> '$.user_id' does not match an index on json_extract(payload, '$.user_id'). Consistency in the application’s query patterns is the recommended discipline.

For frequently-queried fields, a generated column (3.31+) extracts the value once and is then indexed normally:

CREATE TABLE event (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL CHECK (json_valid(payload)),
    user_id INTEGER GENERATED ALWAYS AS (json_extract(payload, '$.user_id')) STORED
);

CREATE INDEX ix_event_user ON event (user_id);

The generated column is computed at insert and update time and stored alongside the row; queries on user_id use the index without referencing the JSON path. This is the most readable form for fields that are accessed repeatedly.

Validation

json_valid(text) returns 1 if the argument is parseable JSON, 0 otherwise. The conventional use is in a CHECK constraint, as shown above.

json_type(json, path) returns the type of a value at a path, one of 'null', 'true', 'false', 'integer', 'real', 'text', 'array', 'object'. The function is useful for queries that branch on the shape of the data:

SELECT id FROM event
WHERE json_type(payload, '$.tags') = 'array';

json_array_length(json, path) returns the length of an array, useful for filtering by collection size.

JSONB

JSONB stores JSON in a compact binary representation. The trade-offs:

Feature	JSON1 (TEXT)	JSONB (BLOB)
Storage	Slightly larger	Slightly smaller
Read performance	Parse on every access	Parse once at write
Write performance	Faster (no encoding)	Slightly slower (encoding step)
Human readability	Yes (TEXT)	No (BLOB)
Round-trip fidelity	Whitespace preserved	Whitespace lost
Compatibility	Universal	SQLite-specific

The recommendation: for new applications where the JSON is consumed primarily through SQL, prefer JSONB. For applications that pass JSON between the database and external systems as text, JSON1 is simpler — the column already contains the right form.

The JSONB functions parallel JSON1: jsonb, jsonb_extract, jsonb_set, jsonb_array, etc. The functions return JSONB values; explicit json() is required to convert to text where text is needed.

SELECT json(jsonb_extract(payload, '$.user'))
FROM event_jsonb
WHERE id = 1;

When to normalise

The recurring question for JSON-storing applications: should this field be a column instead? The decision rule:

If the field is queried with WHERE, ORDER BY, or GROUP BY on a hot path, it is a column. The expression-index workaround works but adds a layer of indirection that the schema declaration would render unnecessary.
If the field is referenced by a foreign key (the JSON contains another row’s primary key), it is a column with a FOREIGN KEY constraint.
If the field is presented as part of every read of the row, it is a column. The JSON parse on every read is wasted work.
If the field is sometimes present and sometimes absent in a way that does not fit a fixed schema, JSON is appropriate.
If the field is a structured payload that is read and written as a unit (an event payload, a user-preferences object, a webhook body), JSON is appropriate.

The rule of thumb: JSON is for opaque structured payloads; columns are for structured fields. A schema that abuses JSON to store columns ends up with the worst of both worlds — the inflexibility of a fixed schema and the opacity of JSON. A schema that pulls the queryable fields into proper columns and stores the rest as JSON is typically the right shape.

A worked example of the hybrid pattern:

CREATE TABLE event (
    id          INTEGER PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES user(id),
    occurred_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    type        TEXT NOT NULL,
    payload     TEXT NOT NULL CHECK (json_valid(payload))
) STRICT;

CREATE INDEX ix_event_user_time ON event (user_id, occurred_at);
CREATE INDEX ix_event_type      ON event (type, occurred_at);

The fields that are queried — user_id, occurred_at, type — are columns with appropriate indexes. The opaque payload — the per-event-type structured data — is stored as JSON. Queries use the columns for filtering and the JSON for projection:

SELECT
    occurred_at,
    payload ->> '$.url'  AS url,
    payload ->> '$.code' AS status_code
FROM event
WHERE user_id = 42
  AND type = 'http_request'
  AND occurred_at >= date('now', '-7 days')
ORDER BY occurred_at DESC
LIMIT 100;

The query plan uses the index for the WHERE clause; the JSON access cost falls on the projection only, where it matters less.