SQLite json_tree() Function

The SQLite json_tree() function recursively walks the specified JSON document, generates a row for each child element (including the parameter itself), and returns a result set consisting of all the rows.

The SQLite json_tree() function is a table-valued function, json_each() is a similar function.

json_each() only walks the immediate children of the JSON object or array, while json_tree() walks all children recursively.

Syntax

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

json_tree(json, path)

Parameters

json: Required. A JSON document.
path: Optional. The path expression.

Return value

The SQLite json_tree() function returns a result set with the following columns:

key: If the JSON is an array, the key column is the index of the array; if the JSON is an object, the key column is the member name of the object; otherwise, the key is NULL.
value: The value of the current element.
type: The JSON type of the current element. Possible values: 'null', 'true', 'false', 'integer', 'real', 'text', 'array', 'object'. They are the same as the return values of json_type().
atom: If value is a primitive JSON value, atom store its corresponding SQL value; otherwise, the column is NULL.
id: An integer identifying the uniqueness of this row.
parent: The integer ID of the parent element.
fullkey: It is the path to the current row element.
path: The path to the parent element of the current row element.

Examples

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

Example: Array

In this example, we use the json_tree() function walk the elements of a JSON array recursively:

SELECT * FROM json_tree('[1, 2, 3]');

key  value    type     atom  id  parent  fullkey  path
---  -------  -------  ----  --  ------  -------  ----
     [1,2,3]  array          0           $        $
0    1        integer  1     1   0       $[0]     $
1    2        integer  2     2   0       $[1]     $
2    3        integer  3     3   0       $[2]     $

Here, the parameter itself also appears in the result set as a row, which key is NULL. The value of the parent column the ID of its parent element.

Example: Multidimensional Array

In this example, we use the json_tree() function walk a multidimensional JSON array recursively:

SELECT * FROM json_tree('[1, 2, [3, [4, 5]]]');

key  value            type     atom  id  parent  fullkey     path
---  ---------------  -------  ----  --  ------  ----------  -------
     [1,2,[3,[4,5]]]  array          0           $           $
0    1                integer  1     1   0       $[0]        $
1    2                integer  2     2   0       $[1]        $
2    [3,[4,5]]        array          3   0       $[2]        $
0    3                integer  3     4   3       $[2][0]     $[2]
1    [4,5]            array          5   3       $[2][1]     $[2]
0    4                integer  4     6   5       $[2][1][0]  $[2][1]
1    5                integer  5     7   5       $[2][1][1]  $[2][1]

Example: Object

In this example, we use the json_tree() function walk a JSON object recursively:

SELECT * FROM json_tree('{"x": 1, "y": {"a": 3, "b": true}}');

key  value                         type     atom  id  parent  fullkey  path
---  ----------------------------  -------  ----  --  ------  -------  ----
     {"x":1,"y":{"a":3,"b":true}}  object         0           $        $
x    1                             integer  1     2   0       $.x      $
y    {"a":3,"b":true}              object         4   0       $.y      $
a    3                             integer  3     6   4       $.y.a    $.y
b    1                             true     1     8   4       $.y.b    $.y

Example: Specify Path

In this example, we use the json_tree() function walk a JSON object specified by a path in a JSON object recursively:

SELECT * FROM json_tree('{"x": 1, "y": {"a": 3, "b": true}}', '$.y');

key  value             type     atom  id  parent  fullkey  path
---  ----------------  -------  ----  --  ------  -------  ----
y    {"a":3,"b":true}  object         4           $.y      $
a    3                 integer  3     6   4       $.y.a    $.y
b    1                 true     1     8   4       $.y.b    $.y