Querying JSON Text With json_extract in SQLite Step by Step

json_extract is the oldest function in SQLite's JSON family and still the one that appears in the most code. It looks simple: a document, a path, a value. The details are in what value, since SQLite has only a handful of types to give back and JSON has a few more. This article walks through what comes back for every JSON type, how paths and multiple paths behave, the cases where a number or a string is not what you expect, and what repeated calls cost.

What comes back for each type

json_extract(doc, path) converts the JSON value at the path into the nearest SQLite type. On the first product:

path               json_extract   typeof     json_type
$.brand            Globex         text       text
$.price            18.88          real       real
$.in_stock         1              integer    true
$.tags             ["sale","eco"] text       array
$.specs.dims.w     11             integer    integer
$.nope             NULL           null       (SQL NULL)

Strings come back as plain SQL text with no quotes. Numbers keep their integer or real nature. Objects and arrays come back as JSON text, minified, and keep their text type; you can pass them to another JSON function. Booleans are the odd case: true and false have no SQLite equivalent, so they become the integers 1 and 0, and the information that they were booleans is gone unless you ask json_type, which says true or false.

json_type(doc, path) is the companion that returns the JSON type name: integer, real, text, true, false, null, array, object. It is the way to tell a JSON null from a missing key, because json_extract returns SQL NULL for both. json_type('{"a":{"b":null}}', '$.a.b') returned null, and json_type('{"a":{}}', '$.a.b') returned SQL NULL.

Paths

A path starts with $. $.a.b goes down keys, $.a[2] takes the third element, and $.a[#-1] takes the last. The # is SQLite's symbol for "the length of the array":

json_extract('{"a":[1,2,3]}', '$.a[1]')    2
json_extract('{"a":[1,2,3]}', '$.a[#-1]')  3
json_extract('{"a":[1,2,3]}', '$.a[#-3]')  1
json_extract('{"a":[1,2,3]}', '$.a[#-4]')  NULL
json_extract('{"a":[1,2,3]}', '$.a[3]')    NULL

An index outside the array returns NULL, not an error. A key containing a space or dot is quoted inside the path: '$."a b"'. Unlike the arrow operators, json_extract insists on the leading $: json_extract('{"a":1}', 'a') raised bad JSON path: 'a'. A NULL path gives NULL.

Several paths at once

With more than one path, the result is a JSON array as text with one element per path:

select json_extract(attrs, '$.price', '$.brand') from products where id = 5;
-- [6,"Acme"]
select json_extract(attrs, '$.tags[0]', '$.tags[5]') from products where id = 5;
-- ["import",null]

A missing path contributes null. The return type is text (typeof said so), which means you have to parse it again to use the pieces, so multi-path extraction is for building output, not for filtering. For a plain query, call json_extract once per field.

Traps

A JSON string is not a number. The document {"a":"1"} holds the text 1. json_extract('{"a":"1"}', '$.a') = 1 returned 0, because a text value and an integer constant do not compare equal. Arithmetic works because SQLite converts: + 1 gave 2. This is the pattern behind a lot of "it works in the report and not in the filter" bugs. Check what the producer writes, or convert on the way out with cast(json_extract(...) as integer).

Large integers. JSON integers up to 9223372036854775807 are exact: 9007199254740993, which a JavaScript client would round, came back unchanged. One more and the value no longer fits in a 64-bit integer: 9223372036854775808 returned the real 9.2233720368547758e+18. And 12345678901234567890 became 1.2345678901234567e+19. Keep larger identifiers in strings.

Decimals are doubles. json_extract('{"a":0.1}', '$.a') + 0.2 is 0.30000000000000004. For money, store cents as integers or the amount as a string, and convert deliberately.

Empty values. json_extract('{"a":[]}', '$.a') returned [] and {"a":{}} returned {}, both text, so where json_extract(doc, '$.tags') is not null is true for an empty array. For an empty string, json_extract('{"a":""}', '$.a') is null is 0 and = '' is 1: an empty string is a value, not a NULL.

JSON5 forms inside the text. SQLite's parser accepts JSON5 in the document: hexadecimal numbers (0x1F gives 31), Infinity and -Infinity (a real and -Inf), single-quoted strings and unquoted keys, and the extraction returns SQL values from them. That is useful for lenient config files and a surprise if you expected a strict parser: a column that stores {"a":Infinity} is not valid RFC 8259 JSON and json_valid says 0 for it by default (validating text as JSON with json_valid).

Unicode. Escapes are decoded: json_extract('{"a":"café 😀"}', '$.a') returned café 😀, and the length() of that is 6 characters, with the surrogate pair counted as one.

Using it in queries

json_extract works wherever an expression does:

select json_extract(attrs, '$.brand') as brand, count(*)
from products
where json_extract(attrs, '$.price') > 450
group by 1
order by 2 desc;

Because the value has a real SQL type, number comparisons work when the JSON holds numbers: > 450 is a numeric comparison. If the producer might write numbers as strings, the comparison turns into text against number, and SQLite orders numbers before text, so a string "500" is never greater than 450. typeof(json_extract(...)) over the column, grouped, shows what you actually have: select typeof(json_extract(attrs, '$.price')), count(*) from products group by 1.

An index on the expression is used only if the query uses the same expression. A query with json_extract(attrs, '$.sku') does not use an index declared on attrs ->> '$.sku', as shown in indexing JSON fields in SQLite with expression indexes.

What repeated calls cost

Every call parses the text, in principle. In practice, SQLite keeps the parsed form of the last JSON value, so several calls on the same document in one row do not pay for the parse each time. I measured a sum over all 200,000 rows:

json_extract calls per row     text column    JSONB column
1                               130 ms
2                               135 ms
4                               199 ms          120 ms

Going from one extraction to two cost 5 ms, and four cost about 70 ms more than one, which is the cost of walking to the keys and not of parsing again. On the JSONB column the four-call query took 120 ms, with the one-call version at 54 ms in a separate run, so the binary form halves the cost (the JSONB format in SQLite 3.45). If you need many fields from each document, json_each reads them in one pass (sum(e.value) over the two dimensions took 110 ms, against 158 ms for two json_extract calls on the same keys), as covered in expanding JSON arrays with json_each and json_tree.

json_extract compared with the arrows

json_extract(doc, path) and doc ->> path give the same SQL value for scalars; -> gives JSON text, always. The operators are shorter and accept a bare key, and json_extract is the only form that takes several paths. For queries that must run on SQLite older than 3.38, json_extract is the only choice. For an expression index, use whichever spelling you will use in every query. The arrow article lists the differences: the SQLite arrow operators for JSON. For a quick look at the document before you write a path, use the JSON formatter.

When json_extract is the wrong function

The value is an array you want as rows. json_extract returns it as one text value. Use json_each.

You need to search anywhere in the document. A path names one place. json_tree walks every node when you do not know the place.

You need a JSON value to pass on, with its type. json_extract turns a boolean into 1 and a string into SQL text, and wrapping it back with json_object loses the boolean. Use -> (JSON text) or json_extract inside json_set, where the value is handled as JSON.

The text may be invalid. json_extract on malformed text raises malformed JSON and aborts the whole query. Guard it with case when json_valid(col) then json_extract(col, '$.a') end, or put a check on the column.

The function is documented in the SQLite manual, json_extract(), and the path syntax under PATH arguments. The overview of all the functions is in JSON functions in SQLite and what you can do with them.