SQLite json_tree() Function

The SQLite json_tree() table-valued function returns one row for the selected JSON value and every descendant beneath it. It is useful when you need to inspect nested objects or arrays as rows.

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

json_each() returns rows for the immediate children of a top-level object or array, or one row if the top-level value is a primitive. json_tree() follows the same rule and continues recursively through nested values.

SQLite includes JSON functions by default starting with version 3.38.0; older builds may need the JSON1 extension enabled. Starting with version 3.45.0, json_tree() also accepts SQLite’s JSONB input format. See the SQLite JSON documentation.

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 stores its corresponding SQL value; otherwise, the column is NULL.
id: An internal integer that differs for each row in the result. Its calculation can change between SQLite releases, so do not store it as a persistent identifier.
parent: The id of the parent row, or NULL for the top-level row selected by json_tree().
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 examples that show how to use json_tree().

Example: Array

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

SELECT key, value, type, atom, fullkey, path
FROM json_tree('[1, 2, 3]');

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

The top-level JSON value is included as the first row; its key and parent are NULL. Descendant rows refer to their parent through parent and id.

Example: Multidimensional Array

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

SELECT key, value, type, atom, fullkey, path
FROM json_tree('[1, 2, [3, [4, 5]]]');

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

Example: Object

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

SELECT key, value, type, atom, fullkey, path
FROM json_tree('{"x": 1, "y": {"a": 3, "b": true}}');

key  value                         type     atom  fullkey  path
---  ----------------------------  -------  ----  -------  ----
     {"x":1,"y":{"a":3,"b":true}}  object         $        $
x    1                             integer  1     $.x      $
y    {"a":3,"b":true}              object         $.y      $
a    3                             integer  3     $.y.a    $.y
b    1                             true     1     $.y.b    $.y

Example: Specify Path

In this example, use the json_tree() function to walk the JSON object selected by a path recursively:

SELECT key, value, type, atom, fullkey, path
FROM json_tree('{"x": 1, "y": {"a": 3, "b": true}}', '$.y');

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