JSON Functions in SQLite and What You Can Do With Them

SQLite has no JSON type. There is no JSON column that validates, no binary format hidden behind the name, no special storage class. JSON is text in a TEXT column (or, since 3.45, a blob in a format called JSONB), and a family of functions reads, edits, builds and unpacks it. That makes SQLite's JSON support unusual: small and simple to learn, and unforgiving about types, because every JSON value becomes an ordinary SQL value the moment you read it. This guide covers the functions in the order you meet them, with outputs from SQLite 3.54.1 on a table of 200,000 documents, the traps marked where they bite, and the numbers that say how much the text-versus-binary choice matters.

It is the hub for a set of shorter articles: json_extract, the arrow operators, json_each and json_tree, json_group_array, expression indexes, the JSONB format, loading a file, settings in JSON and json_valid.

What there is, and what there is not

SQLite is dynamically typed. A column may hold text, a number, a blob or NULL, and nothing about a TEXT column says "this is JSON". The JSON functions take a text argument, parse it on every call, and return text or an SQL scalar. The consequences:

• There is no JSON column type. A check (json_valid(attrs)) constraint is how you make the column reject garbage; without it, any text goes in.

• Every function parses the document each time, so a query that calls json_extract on three keys parses the text three times. Part of the performance story below is how to avoid that.

• Output is minified. json('{"a" : 1, "b": [1, 2]}') returned {"a":1,"b":[1,2]}, with no spaces. Key order and duplicates are preserved by the text functions: json('{"b":1,"a":2}') stayed in that order and json('{"a":1,"a":2}') kept both keys.

• The functions are strict about arguments but liberal about input. SQLite accepts JSON5 in most functions: json('{a:1}') returned {"a":1}, with an unquoted key turned into a quoted one.

The core set, by job:

The list is longer than the table shows. On 3.54.1, select count(distinct name) from pragma_function_list where name like 'json%' returned 30 names, and they include json_pretty for indented output, json_array_insert for placing an element at a position and json_error_position for finding syntax errors. json_each and json_tree are table-valued functions and do not appear in that list. The JSON functions have been built in by default since 3.38.0, so a build that lacks them was compiled with them switched off. If a query fails with no such function: json_extract, check the version and the compile options before you check your SQL.

Reading values

json_extract(doc, path) takes a path and returns an SQL value. The arrows are the newer way:

select json_extract(attrs, '$.brand') a,
       attrs -> '$.brand'             b,
       attrs ->> '$.brand'            c
from products where id = 1;

a       b          c
Globex  "Globex"   Globex

json_extract and ->> give the SQL text Globex. -> gives JSON text, so the string keeps its quotes, and on an object or array it gives the JSON of that part. The difference between the three matters mostly for strings, because for numbers json_extract and ->> both give a real SQL number: the price came back as the real 18.88 from both, and attrs -> '$.price' gave 18.88 as JSON text.

Booleans and nulls are where the types diverge. json_extract of true returned the integer 1 (SQLite has no boolean), and -> returned the text true. A JSON null read with -> returned the JSON text null, which is the four characters, while ->> and json_extract return an SQL NULL. A missing path returns SQL NULL from all three.

Paths follow $.key, $.array[0] and $.a.b.c, with SQLite's own extension [#-1] for "last element": attrs -> '$.tags[#-1]' returned "eco". A key with a space or a dot needs quotes in the path: '$."a b"'. As a convenience, -> and ->> accept a bare label on the right: '{"a":1}' -> 'a' is the same as -> '$.a'. A bare label is a literal key, not a path, so -> 'a.b' looked for a key named a.b and returned nothing. The details are in querying JSON text with json_extract and the arrow operators.

The comparison traps

SQLite's comparison rules make two classic mistakes quiet.

Text versus number. ->> returns an SQL value with no column affinity, and SQLite orders numbers before text. So attrs ->> '$.price' > '450' compared a real with a text constant, and no number is greater than text: the count was 0. The same filter with a number, > 450, counted 19,856. This is the reverse of the MySQL and PostgreSQL case, where text comparison gives a wrong non-zero count. Write the right literal type.

Large integers. SQLite reads JSON numbers as 64-bit integers or doubles. json_extract('{"a":12345678901234567890}', '$.a') returned 1.2345678901234567e+19, a real, because the value does not fit in a signed 64-bit integer. If you store identifiers larger than 2^63, keep them as strings.

And json_extract('{"a":0.1}', '$.a') + 0.2 is 0.30000000000000004: JSON numbers are doubles.

Editing

The editing functions return a new document, and you assign it back:

update products set attrs = json_set(attrs, '$.price', 9.9) where id = 1;

The three set functions differ in what they do for a path that exists and one that does not, as elsewhere: json_set replaces or adds, json_insert only adds, json_replace only replaces. SQLite has two behaviors that differ from MySQL and PostgreSQL:

• It creates missing parents. json_set('{"a":1}', '$.b.c', 2) returned {"a":1,"b":{"c":2}}. The other two databases leave the document unchanged. This is friendlier and surprising if you port code the other way.

• **Array position # appends.** json_set('{"a":[1,2]}', '$.a[#]', 9) gave {"a":[1,2,9]}, while $.a[5] past the end did nothing and $.a[#-1] replaced the last element.

Values need the right type. A SQL string becomes a JSON string, so json_set('{"a":1}', '$.s', '{"x":1}') stored the text "{\"x\":1}". To store an object, wrap it with json(), or build it with json_object: json_set(..., json('{"x":1}')) gave {"x":1} nested. A boolean comparison like 1 = 1 stored 1, not true; use json('true'). json_remove takes several paths, and json_patch(doc, patch) applies an RFC 7396 merge patch, with null removing a key and arrays replaced whole.

Building

json_object('a', 1, 'b', null, 'c', json('[1]')) returned {"a":1,"b":null,"c":[1]}. The json() call matters: without it, a text value that looks like JSON becomes a quoted string. The same holds for json_array. json_quote('a"b') returns "a\"b", and json_quote(json('[1]')) returns [1] untouched. Duplicate keys are not collapsed: json_object('a', 1, 'a', 2) gave {"a":1,"a":2}, so do not construct objects with repeated names.

Unpacking with json_each and json_tree

json_each is a table-valued function that returns one row per element of an array or per key of an object, with key, value, type, atom, fullkey and path columns. It is the SQLite answer to jsonb_array_elements and JSON_TABLE:

select p.id, e.key, e.value
from products p, json_each(p.attrs, '$.tags') e
where p.id = 1;

returned 1|0|sale and 1|1|eco. json_tree walks the document recursively: on {"a":1,"b":[1,2],"c":{"d":null}} it returned seven rows from the root down to $.c.d, with the full path in fullkey. Filtering on atom is not null lists the leaves. Expanding JSON arrays with json_each and json_tree goes through the columns.

Aggregating

json_group_array(x) collects values into an array and json_group_object(k, v) into an object. Properties verified on 3.54.1:

• An empty set returns [], not NULL, which is gentler than MySQL and PostgreSQL.

• DISTINCT and, since 3.44, ORDER BY inside the call work: json_group_array(id order by id desc) returned [3,2,1].

• A column holding JSON text is added as a string unless you wrap it in json(). json_group_array(attrs) returned an array containing one escaped string, ["{\"sku\":...}"], while json_group_array(json(attrs)) embedded the real object. Forgetting json() is the commonest bug here, and the output looks almost right. See building JSON results with json_group_array.

Indexing

You index a JSON field with an expression index, and the query has to use the same expression for the planner to see it:

create index idx_sku on products ((attrs ->> '$.sku'));

With it, explain query plan select id from products where attrs ->> '$.sku' = 'SKU-123456' printed SEARCH products USING COVERING INDEX idx_sku (<expr>=?) and the lookup took 0.17 ms. Without the index, the same query was SCAN products and took 139 ms. Three rewrites of the query that look equivalent did not use the index and scanned the table: json_extract(attrs, '$.sku') = ..., attrs -> '$.sku' = '"SKU-123456"' and attrs ->> 'sku' (the bare label). Choose one spelling for the field, use it in the index and in every query, and consider hiding it in a view. A generated column names the expression: alter table products add column brand text generated always as (attrs ->> '$.brand') virtual plus an index on brand gave SEARCH products USING INDEX idx_brand (brand=?) for where brand = 'Acme'. SQLite does not rewrite the JSON form to the column, so where attrs ->> '$.brand' = 'Acme' still scanned. Adding a stored generated column with ALTER TABLE failed with cannot add a STORED column, only virtual ones can be added after the fact. Numeric ranges work through an index on attrs ->> '$.price': the range between 499.9 and 500 returned its 31 rows from SEARCH ... USING COVERING INDEX idx_price. The index sizes were 3.6 MB for the SKU, 3.2 MB for the price and 2.9 MB for the brand column, on a 35.5 MB table. The full treatment is in indexing JSON fields in SQLite with expression indexes.

JSONB

SQLite 3.45 added a binary encoding of JSON. jsonb(text) converts, json(blob) converts back, and most functions have a jsonb_* twin that returns the binary form. A blob is accepted anywhere a JSON text is: json_extract(jsonb('{"a":1}'), '$.a') returned 1.

The binary form is smaller because it stores no whitespace and avoids re-parsing: the 200,000 documents were 30.6 MB of text and 23.2 MB as JSONB blobs, 24 percent less, and the tables came out at 35.5 MB and 24.5 MB. Reads were faster: a filter on a nested key (attrs ->> '$.specs.dims.w' = 20) took 121 ms on the text table and 42 ms on the JSONB one, and a group-by over brand with the average price took 327 ms against 136 ms. The detail articles cover the conversions and the pitfalls: using the JSONB format added in SQLite 3.45. The blob is opaque to anything but SQLite's own functions, so you cannot read a JSONB column in a generic database viewer, and you cannot select it for a web response without json().

For comparison, the same data as typed columns was 11.8 MB, and the group-by took 47 ms. The progression is text 35.5 MB and 327 ms, JSONB 24.5 MB and 136 ms, columns 11.8 MB and 47 ms. The binary format closes about half of the gap with real columns and keeps the flexibility.

Validating and what counts as JSON

json_valid(x) returns 1 for valid text and 0 otherwise, and NULL for NULL. The default is strict RFC 8259: '{a:1}', '{"a":1,}' and 'NaN' are all 0. An optional second argument is a bitmask of acceptable forms: 1 for RFC 8259 text, 2 for JSON5 text, 4 for a blob that is probably JSONB, 8 for a strictly valid JSONB blob. So json_valid('{a:1}', 2) is 1 and json_valid(jsonb('{"a":1}'), 8) is 1 while json_valid(jsonb('{"a":1}')) is 0, since the default flag asks for text. json_error_position('{"a": tru}') returned 7, the position of the problem, and 0 for valid input. Use check (json_valid(attrs)) on the column. Validating text as JSON with json_valid covers the flags and the case where a constraint cannot be added to an existing table.

The functions themselves are more lenient than json_valid: they accept JSON5 text and raise malformed JSON only for what JSON5 also rejects. A column that passes a strict check is safe; a column that does not may contain forms that only SQLite understands.

Getting data in

The sqlite3 shell has readfile(), and the JSON functions accept its result directly: json_array_length(readfile('users.json'), '$.users') returned 6. Combined with json_each, a file becomes rows in one statement. Loading a JSON file into SQLite with readfile shows it, along with the JSON Lines route. Applications that embed SQLite use their own binding: check that the library's SQLite version has what you need, since Python's sqlite3 module in my container was 3.40.1, which has the arrow operators and no JSONB.

A worked example: a document into tables

Most real jobs start with a document and end with tables. I used a small file, users.json, with a generated date and a users array of six objects, each with an id, a name, an email, a roles array, an address object and an active flag. In the sqlite3 shell, readfile() reads it, and json_each turns the array into rows:

create table users (id integer primary key, name text, email text, city text, active integer, roles text);

insert into users
select u.value ->> '$.id', u.value ->> '$.name', u.value ->> '$.email',
       u.value ->> '$.address.city', u.value ->> '$.active', u.value -> '$.roles'
from json_each(cast(readfile('users.json') as text), '$.users') u;

The result was six rows. The city column came from a nested object with a two-level path, active arrived as the integer 0 or 1 (JSON true and false become integers through ->>), and roles stayed as JSON text, ["viewer","editor"], because I used -> for it. Keeping a sub-array as JSON text is a legitimate choice when you rarely query it. When you do need it as rows, a second json_each over the first one's value unpacks it:

select u.value ->> '$.id' as id, r.value as role
from json_each(cast(readfile('users.json') as text), '$.users') u,
     json_each(u.value, '$.roles') r
order by 1, 2;

That printed 1|editor, 1|viewer, 2|editor, 2|viewer, 3|admin, and so on: two rows for users with two roles, one for users with one. The second json_each is evaluated for each row of the first, which is the same lateral behavior as in the other databases, and an empty array would produce no rows for that user (use a left join if you want to keep them). json_array_length(u.value, '$.roles') counts without unpacking. This pattern is all you need to normalize most JSON that arrives from APIs.

Searching inside arrays and nested documents

SQLite has no "contains" operator for JSON arrays, so membership is a subquery on json_each:

select count(*) from products
where exists (select 1 from json_each(attrs, '$.tags') where value = 'gift');

It returned 33,333 rows. I compared four spellings on the 200,000-row text table, with a full scan in each case:

exists (select ... json_each ...)              145 ms
attrs -> '$.tags' like '%"gift"%'               120 ms
attrs ->> '$.tags' like '%gift%'                115 ms
'gift' in (select value from json_each(...))    158 ms
exists (...) on the JSONB table                  78 ms

The LIKE forms were slightly faster and are wrong in subtle ways: they match the text gift anywhere in the array's JSON, including inside a longer word such as giftwrap. The json_each forms are exact. On the JSONB table the exact form took 78 ms, nearly half the text time, which shows what skipping the text parse is worth. None of them can use an index; to search by tag often, put the tags in a separate table with an index, or pull the first tag into an indexed expression if only one matters.

To find a key anywhere in a document, json_tree walks every node: exists (select 1 from json_tree(attrs) where key = 'color' and value = 'red') found 50,000 products by their color, which sits at $.specs.color, without naming the path. It is flexible and slow, since it visits every node of every document, so reserve it for exploration.

Dates and times

JSON has no date type, and SQLite's date functions read ISO 8601 text, so dates in documents work out of the box when they are stored in that form. date('{"d":"2026-10-26T12:30:00Z"}' ->> '$.d') returned 2026-10-26, and datetime(...) on a "2026-10-26 12:30:00" string returned it normalized. Other forms fail without an error: date('{"d":"26/10/2026"}' ->> '$.d') returned NULL, and a Unix timestamp such as 1761480000 returned NULL until I added the modifier, date(... ->> '$.d', 'unixepoch'), which gave 2025-10-26. A NULL from a date function on a JSON field means "I could not read that format", and it is easy to mistake for a missing value. Writing dates into documents is the other direction: json_object('d', date('2026-10-26')) produced the string "2026-10-26". Standardize on ISO 8601 with a Z or an explicit offset and the rest takes care of itself, as discussed in JSON dates lack a date type.

At the command line

The sqlite3 shell can emit JSON, with -json or .mode json: sqlite3 -json lite.db "select id, attrs ->> '$.brand' as brand from products where id <= 2" printed an array of two objects. One limitation surprised me. A JSON value that comes from -> or a nested column is output as an escaped string, not as a nested structure: the tags column came out as "[\"sale\",\"eco\"]", even when I wrapped it in json(), because the shell does not carry the "this is JSON" marker into its output. For nested output, build the whole document in SQL with json_group_array(json_object(...)) and print it in list mode, where it is printed as is. For CSV, -csv -header works as expected, and -line prints one column: value pair per line, which is handy for looking at a single row.

A one-liner to turn a JSON file into rows and a CSV is sqlite3 -csv -header :memory: "select value ->> '$.id' as id, value ->> '$.name' as name from json_each(cast(readfile('users.json') as text), '$.users')", which printed the id and name of each user. It needs no import step and no temporary file. Reading from standard input with readfile('/dev/stdin') did not work in my test (out of memory), so read from a file. For more on loading, see loading a JSON file into SQLite with readfile.

Errors and what they mean

SQLite's JSON error messages are short, and a handful cover almost every failure:

select json_extract('{bad', '$.a');         malformed JSON
select json_extract('{"a":1}', 'a');         bad JSON path: 'a'
select json_object('a');                      json_object() requires an even number of arguments
select json_object(1, 2);                     json_object() labels must be TEXT
select json_set('{"a":1}', '$.b');            json_set() needs an odd number of arguments
select json_extract('', '$.a');               malformed JSON

malformed JSON is the error you will see most, and it comes with no position. When a query over a table dies with it, one row holds bad text, and json_error_position(col) on the rows where json_valid(col) is 0 tells you where in the text the problem is. Find the rows first: select id, json_error_position(attrs) from products where not json_valid(attrs). bad JSON path means the second argument does not start with $ (the arrows accept a bare label as a shortcut, but json_extract does not). The argument-count errors are programmer mistakes caught early, and json_object() labels must be TEXT catches the case of passing a number as a key.

Several things that look wrong are not errors. A path that finds nothing returns NULL: json_extract('[1,2]', '$.a'), json_extract('{"a":1}', '$[0]') and a NULL document all gave NULL. A document that is NULL propagates NULL. So an empty result is ambiguous between "the key is missing", "the document is NULL" and "the path is for the wrong shape", and the only way to tell is to look at json_type of the parent. One more rule worth knowing: the functions that take text fail on an empty string, because '' is not valid JSON, while a NULL passes through. An import that writes '' for "no value" will break the first query that touches the column. Store NULL.

A checklist

1. Put a check (json_valid(col)) on every JSON text column.

2. Pick one spelling for each extracted field (->> is the shortest) and use it everywhere, including the index.

3. Compare extracted values with the right literal type: numbers with numbers, text with text.

4. Wrap JSON text in json() when it goes into json_object, json_array or json_group_array.

5. Consider JSONB for large tables you query often, and keep JSON text for tables you read in other tools.

6. Check sqlite_version() in every environment that runs your queries.

7. For an overview of how SQLite compares with the other databases for this job, see JSON in SQL Server, Oracle, Snowflake and BigQuery compared and, for the server databases, working with JSONB in PostgreSQL and using the JSON column type in MySQL. A quick way to look at a document before writing a path is the JSON formatter.

When SQLite JSON is the wrong tool

You need to query one set of fields all the time. The savings from JSONB are real, and typed columns were still three times faster and three times smaller than JSONB.

You need a real JSON type. No validation without a check constraint, no way to tell a JSON column from a text column in the schema, and no operators beyond the functions. If other tools read the database, they see text or an opaque blob.

Documents are large and updated often. An update rewrites the whole document in the row, and every index on the JSON expressions is recomputed. Updating the price in 20,000 rows took 0.21 seconds with three JSON indexes on the table, 0.14 seconds after dropping them and 0.08 seconds on the JSONB copy. Index only what you query.

You need concurrent writers. SQLite serializes writers, so a hot document updated by many processes is a bottleneck whatever its format.

The deployment has an old SQLite. Many system and language bundled versions predate 3.38 (arrows) and 3.45 (JSONB). Check before designing around either.

The reference is the SQLite documentation: JSON functions and operators, including the JSONB and JSON5 sections, and the JSONB format specification. For choosing between a JSON column and a document store, read choosing between a JSON column and a document database.