JSON Functions

JSON is text, and that is the whole model

SQLite has no JSON type. JSON lives in a TEXT column and the JSON1 functions parse it on every call. They have been built into the amalgamation since 3.38, so no extension load is needed.

CREATE TABLE docs(
  id   INTEGER PRIMARY KEY,
  meta TEXT NOT NULL CHECK(json_valid(meta))
) STRICT;
INSERT INTO docs(meta) VALUES('{"name":"Ada","tags":["sql","c"],"age":36}');

The CHECK(json_valid(meta)) is the standard guard. Without it a malformed string is stored happily and every later json_extract on that row raises.

SELECT json('{ "a" : 1 }');           -- {"a":1}, minified and validated
SELECT json('{bad');                  -- Error: malformed JSON
SELECT json_valid('{bad');            -- 0
SELECT json_valid('{"a":1}');         -- 1
SELECT json_error_position('{"a":1,,}');   -- 8, the 1-based byte of the error
SELECT json_pretty('{"a":1}');        -- indented output, 3.46+

json() is the minify-and-validate function. Run it on input you are about to store so that two logically identical documents compare equal as text.

Extraction: json_extract, ->, and ->>

json_extract(doc, path) returns a SQL value for a scalar and a JSON text for an object or array. The operators (3.38+) are the compact form, and the difference between them is the single most important thing on this page.

SELECT '{"a":1}' -> '$.a',   typeof('{"a":1}' -> '$.a');    -- 1|text
SELECT '{"a":1}' ->> '$.a',  typeof('{"a":1}' ->> '$.a');   -- 1|integer
SELECT '{"a":"x"}' -> '$.a';    -- "x"   <- quotes included
SELECT '{"a":"x"}' ->> '$.a';   -- x     <- what you actually want
SELECT '{"a":{"b":1}}' -> '$.a';   -- {"b":1}
SELECT '{"a":{"b":1}}' ->> '$.a.b';  -- 1

Use ->> in a WHERE, a SET, or an index. Use -> only to walk deeper. Comparing a -> result to a plain string fails because of the embedded quotes, so WHERE meta -> '$.name' = 'Ada' never matches while WHERE meta ->> '$.name' = 'Ada' does.

Both operators also accept a bare integer or a bare key name as a shorthand path.

SELECT '[1,2,3]' -> 1;              -- 2, a 0-based array index
SELECT '[1,2,3]' ->> '$[0]';        -- 1
SELECT '[1,2,3]' ->> '$[#-1]';      -- 3, '#' is the array length
SELECT '{"a":1}' ->> 'a';           -- 1, a bare key means $.a

json_extract with multiple paths returns a JSON array, and with the single path '$' it returns the whole document.

SELECT json_extract('{"a":1,"b":2}','$.a','$.b');   -- [1,2]
SELECT json_extract('{"a":1}','$');                 -- {"a":1}
SELECT json_extract('{"a":"x"}','$.a');             -- x, unquoted like ->>
SELECT json_extract('{"a":1}','$.missing');         -- (NULL), not an error

Path syntax: $ is the root, .key a member, ["key with spaces"] a quoted member, [n] an array element, and [#-n] counts from the end. An unmatched path yields NULL rather than raising, so a typo is silent.

Building JSON

SELECT json_array(1, 'x', NULL, json_array(2));
-- [1,"x",null,[2]]
SELECT json_object('a', 1, 'b', json('[1,2]'));
-- {"a":1,"b":[1,2]}
SELECT json_object('a', json_array(1,2));
-- {"a":[1,2]}
SELECT json_quote('x'), json_quote(3.5);
-- "x"|3.5

The trap: passing a JSON string into json_object embeds it as a quoted string, so wrap it in json() to nest it as structure.

SELECT json_object('a', '[1,2]');          -- {"a":"[1,2]"}   <- escaped text
SELECT json_object('a', json('[1,2]'));    -- {"a":[1,2]}     <- nested array

Arguments to json_object must be an even count of key-value pairs, and a duplicate key keeps the last value. json_quote is how you turn a SQL value into a JSON value, and it leaves an existing JSON value from json() alone.

Modifying JSON

All four return a new document and never mutate in place. They accept multiple path-value pairs, applied left to right.

SELECT json_set('{"a":1}',     '$.a', 9, '$.b', 2);  -- {"a":9,"b":2}
SELECT json_insert('{"a":1}',  '$.a', 9, '$.b', 2);  -- {"a":1,"b":2}
SELECT json_replace('{"a":1}', '$.a', 9, '$.b', 2);  -- {"a":9}
SELECT json_remove('{"a":1,"b":2}', '$.b');          -- {"a":1}
SELECT json_patch('{"a":1,"b":2}', '{"b":null,"c":3}');  -- {"a":1,"c":3}

json_patch is the merge-patch semantic: a null in the patch deletes the key, and nested objects merge recursively while arrays are replaced wholesale. That is exactly what you want for a PATCH endpoint and exactly wrong if you need to store a literal null.

Appending to an array uses the [#] path, which means "one past the end".

UPDATE docs SET meta = json_insert(meta, '$.tags[#]', 'rust') WHERE id = 1;
UPDATE docs SET meta = json_set(meta, '$.age', (meta ->> '$.age') + 1) WHERE id = 1;
UPDATE docs SET meta = json_remove(meta, '$.tags[0]') WHERE id = 1;

Every update rewrites and reparses the whole document, so a hot counter belongs in a real column rather than inside JSON.

Inspecting shape

SELECT json_type('{"a":[1]}');            -- object
SELECT json_type('{"a":[1]}','$.a');      -- array
SELECT json_type('{"a":[1]}','$.a[0]');   -- integer
SELECT json_type('{"a":1}','$.zz');       -- (NULL), path not found
SELECT json_array_length('[1,2,3]');      -- 3
SELECT json_array_length('{"a":[1,2]}','$.a');  -- 2
SELECT json_array_length('{"a":1}');      -- 0, not an array

json_type returns one of null, true, false, integer, real, text, array, object. Note that true and false are distinct types, so a JSON boolean does not come back as integer. Extracting one gives SQL 1 or 0.

Aggregating rows into JSON

SELECT json_group_array(name) FROM users;
-- ["Ada","Grace"]
SELECT json_group_object(k, v) FROM settings;
-- {"theme":"dark","lang":"en"}
SELECT json_group_array(json_object('id', id, 'name', name)) FROM users;
-- [{"id":1,"name":"Ada"},{"id":2,"name":"Grace"}]

That last form is how you return a whole nested result set in one round trip, which removes the N+1 query entirely.

SELECT json_object(
  'id',    u.id,
  'email', u.email,
  'posts', (SELECT json_group_array(json_object('id', p.id, 'title', p.title))
            FROM posts p WHERE p.author_id = u.id)
) AS doc
FROM users u WHERE u.id = ?;

json_group_array skips nothing, so a NULL becomes a JSON null. Add FILTER (WHERE x IS NOT NULL) when you want them gone, and note that the aggregate over zero rows returns [] rather than NULL.

json_each and json_tree

Both are table-valued functions, so they go in the FROM clause. json_each walks one level, json_tree walks the whole document recursively.

SELECT key, value, type, atom, fullkey, path
FROM json_each('{"a":1,"b":[10,20]}');
-- a|1|integer|1|$.a|$
-- b|[10,20]|array||$.b|$

SELECT fullkey, type, atom FROM json_tree('{"a":1,"b":[10,20]}');
-- $|object|
-- $.a|integer|1
-- $.b|array|
-- $.b[0]|integer|10
-- $.b[1]|integer|20

Joining a table to json_each is the idiom for querying inside an array. The second argument narrows the walk to a subpath.

-- Every doc that has the tag 'sql'
SELECT DISTINCT d.id
FROM docs d, json_each(d.meta, '$.tags') AS t
WHERE t.value = 'sql';

-- Tag frequency across all docs
SELECT t.value AS tag, count(*) AS n
FROM docs d, json_each(d.meta, '$.tags') AS t
GROUP BY 1 ORDER BY n DESC;

-- Explode a JSON array parameter into rows, no temp table needed
SELECT * FROM users WHERE id IN (SELECT value FROM json_each(:ids_json));

-- Find every path in the corpus whose value is a string, with json_tree
SELECT DISTINCT fullkey FROM docs, json_tree(docs.meta) WHERE type = 'text';

The IN (SELECT value FROM json_each(:ids)) pattern is worth memorizing. It sidesteps the SQLITE_MAX_VARIABLE_NUMBER limit on a long IN (?, ?, ?, ...) list by binding one JSON string instead.

json_each on a scalar yields exactly one row, and on a NULL it yields none, so a missing array degrades to zero rows rather than to an error.

JSONB, and indexing a JSON path

jsonb (3.45+) is SQLite's internal binary representation, stored as a BLOB. It skips the parse step on every read, so it is faster to query and usually slightly smaller.

SELECT typeof(jsonb('{"a":1}'));        -- blob
SELECT json(jsonb('{"a":1}'));          -- {"a":1}, converts back to text
SELECT jsonb_extract('{"a":1}','$.a');  -- 1
SELECT length(jsonb('{"a":1}')), length('{"a":1}');   -- 5|7

Every json_* function has a jsonb_* twin (jsonb_set, jsonb_insert, jsonb_replace, jsonb_remove, jsonb_patch, jsonb_array, jsonb_object, jsonb_group_array, jsonb_group_object) that returns JSONB instead of text. The readers (json_extract, ->, ->>, json_each, json_type) accept either representation transparently.

CREATE TABLE docs2(id INTEGER PRIMARY KEY, meta BLOB NOT NULL) STRICT;
INSERT INTO docs2(meta) VALUES(jsonb('{"name":"Ada","age":36}'));
SELECT meta ->> '$.name' FROM docs2;    -- Ada, the operators just work
SELECT json(meta) FROM docs2;           -- for sending to a client

The tradeoffs: JSONB is not a documented on-disk format guaranteed across versions, it is unreadable in a .dump, and a client library will hand it back as raw bytes. Store text when the database is a shared artifact, store JSONB when read throughput is what matters and the application is the only reader.

Indexing is where JSON in SQLite becomes usable at scale. An expression index on the extraction makes a lookup an index seek.

CREATE INDEX idx_docs_name ON docs(meta ->> '$.name');
EXPLAIN QUERY PLAN SELECT id FROM docs WHERE meta ->> '$.name' = 'Ada';
-- SEARCH docs USING INDEX idx_docs_name (<expr>=?)

Three rules for that to actually fire:

- The query must repeat the expression character for character. An index on meta ->> '$.name' is not used by json_extract(meta,'$.name') = 'Ada', even though the two are equivalent, and it is not used by meta -> '$.name' either. - The expression must be deterministic, which JSON extraction is. - A partial index keeps it small when only some documents have the key.

CREATE INDEX idx_docs_age ON docs(CAST(meta ->> '$.age' AS INTEGER))
  WHERE meta ->> '$.age' IS NOT NULL;

For an array you cannot index the elements directly. Either denormalize into a real join table, which is almost always the right answer, or maintain a generated column plus an index for the one path you query most.

ALTER TABLE docs ADD COLUMN name TEXT AS (meta ->> '$.name');   -- VIRTUAL
CREATE INDEX idx_docs_name2 ON docs(name);
SELECT id FROM docs WHERE name = 'Ada';    -- reads like a normal column

That generated-column form is usually the nicer one to live with, since queries stay readable and the index is on a plain column name. STORED would also work but ALTER TABLE ADD COLUMN only accepts VIRTUAL.