SQLite json_extract() Function

The SQLite json_extract() function reads one or more values from JSON using a path such as $.user.name or $.items[0]. Unlike MySQL’s function of the same name, SQLite can return ordinary SQL scalar values rather than JSON text.

Syntax

json_extract(json_document, path)
json_extract(json_document, path1, path2, ...)

• json_document is a well-formed JSON value stored as text (or a supported SQLite JSONB value).
• path identifies a JSON value. $ means the root; .name selects an object member; [0] selects the first array element; and [#-1] selects the last array element.
• Additional paths are optional. When there are multiple paths, the result is a JSON array stored as SQL TEXT.

An invalid JSON document or malformed path raises an error. Use json_valid() to validate text before extracting values.

Return types: SQL values versus JSON text

The result depends on the number of paths and the type of the selected JSON value:

With two or more paths, SQLite returns one JSON array as TEXT, preserving a null entry for any missing path.

Extract a nested property

SELECT json_extract(
    '{"customer":{"name":"Ada","city":"London"}}',
    '$.customer.name'
) AS customer_name;

customer_name
-------------
Ada

A single string property is returned as regular SQL text, so it can be compared, filtered, or sorted without removing surrounding JSON quotes.

Check the SQL type of extracted values

SELECT
    typeof(json_extract('{"age":37}', '$.age')) AS age_type,
    typeof(json_extract('{"active":true}', '$.active')) AS active_type,
    typeof(json_extract('{"tags":["sql","json"]}', '$.tags')) AS tags_type;

age_type  active_type  tags_type
--------  -----------  ---------
integer   integer      text

The extracted array ["sql","json"] is JSON text. Its SQLite storage class is TEXT, not a separate JSON storage class.

Extract an array item or the last item

SELECT
    json_extract('{"scores":[10,20,30]}', '$.scores[0]') AS first_score,
    json_extract('{"scores":[10,20,30]}', '$.scores[#-1]') AS last_score;

first_score  last_score
-----------  ----------
10           30

Extract multiple paths

SELECT json_extract(
    '{"name":"Ada","age":37}',
    '$.name',
    '$.age',
    '$.missing'
) AS selected_values;

selected_values
-----------------
["Ada",37,null]

With multiple paths, the function returns a JSON array as SQL text, even though a single $.age path would return SQL integer 37.

JSON null versus a missing path

Both JSON null and a missing path produce SQL NULL when extracted with one path. To tell them apart, use json_type():

SELECT
    json_type('{"note":null}', '$.note') AS null_member_type,
    json_type('{"note":null}', '$.missing') AS missing_member_type;

null_member_type  missing_member_type
----------------  -------------------
null              NULL

The first value is the text 'null', identifying a JSON null. The second is SQL NULL, identifying an absent path.

SQLite versus MySQL json_extract()

SQLite and MySQL do not always return the same kind of result:

MySQL returns JSON values; SQLite returns SQL scalars for strings, numbers, booleans, and null when a single path is provided. This distinction matters when moving queries between database engines.

For JSON text output in SQLite, use the -> operator where appropriate; for SQL scalar output, use json_extract() with a single scalar path or the ->> operator.

Frequently asked questions

Why does json_extract() return NULL?

The requested path may not exist, the selected JSON value may be null, or an input argument may be SQL NULL. Check the path and use json_type() when you need to distinguish missing values from JSON null.

Can json_extract() read JSON arrays?

Yes. Paths such as $[0], $.items[2], and $.items[#-1] access array elements. Extracting a whole array returns JSON text.

How do I test JSON queries without installing SQLite?

Open the SQLite Playground to run and edit these examples in a real SQLite engine in your browser.

For detailed semantics and compatibility notes, see the official SQLite JSON documentation.