SQLite json_extract() Function

The SQLite json_extract() function extracts the value specified by the path expression from the JSON document and returns it.

Syntax

Here is the syntax of the SQLite json_extract() function:

json_extract(json, path, ...)

Parameters

json

Required. A JSON document.

path

Required. You should specify at least one path expression.

Return value

The json_extract() function returns all the values ​​in the JSON document matched by the path expression. If the path expression matches a value, return that value, if the path expression matches more than one value, return an array containing all the values.

The json_extract() function will return NULL in the following cases:

• If the specified path does not exist in the JSON document.
• If any parameter is NULL.

SQLite will give an error if the parameter json is not a valid JSON document. You can use json_valid() to verify JSON documents.

Examples

Here are some examples to show the usages of json_extract().

Example: JSON Array

The following statement shows how to extract an element from a JSON array using SQLite json_extract():

SELECT json_extract('[1, 2, {"x": 3}]', '$[2]');

json_extract('[1, 2, {"x": 3}]', '$[2]')
----------------------------------------
{"x":3}

Let’s see another example with multiple path parameters:

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

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

Example: JSON Object

The following statement shows how to extract the value of a node from a JSON object using SQLite json_extract():

SELECT json_extract('{"x": 1, "y": [1, 2]}', '$.y');

json_extract('{"x": 1, "y": [1, 2]}', '$.y')
--------------------------------------------
[1,2]

Let’s see another example with multiple path parameters:

SELECT json_extract('{"x": 1, "y": [1, 2]}', '$.x', '$.y');

json_extract('{"x": 1, "y": [1, 2]}', '$.x', '$.y')
---------------------------------------------------
[1,[1,2]]

Other examples

Let’s look at more examples:

SELECT
    json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$'),
    json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c'),
    json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]'),
    json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'),
    json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a'),
    json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]'),
    json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'),
    json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a'),
    json_extract('{"a":"xyz"}', '$.a'),
    json_extract('{"a":null}', '$.a');

         json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') = {"a":2,"c":[4,5,{"f":7}]}
       json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') = [4,5,{"f":7}]
    json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') = {"f":7}
  json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') = 7
    json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') = [[4,5],2]
     json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') = 5
       json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') =
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') = [null,2]
                     json_extract('{"a":"xyz"}', '$.a') = xyz
                      json_extract('{"a":null}', '$.a') =