SQLite JSON_TREE()

Posted on by Ian

In SQLite, json_tree() is a table-valued function that walks the JSON value provided as its first argument and returns a table consisting of one row for each array element or object member.

We provide the JSON value as an argument when we call the function.

We can optionally pass a second argument, which specifies a path to start from. When we do this, json_tree() treats that path as the top-level element.

The json_tree() function recursively walks through the JSON substructure starting with the top-level element. To walk only the immediate children of the top-level array or object, or just the top-level element itself if the top-level element is a primitive value use the json_each() function instead.

Syntax

We can use the json_tree() function in the following ways:

json_tree(X)
json_tree(X,P)

Where X represents the JSON, and P is an optional argument that represents the path to treat as the top-level.

Example

Here’s an example to demonstrate how it works:

SELECT * FROM json_tree('{ "name" : "Woof", "age" : 10 }');

Result:

+------+--------------------------+---------+------+----+--------+---------+------+
| key  |          value           |  type   | atom | id | parent | fullkey | path |
+------+--------------------------+---------+------+----+--------+---------+------+
| null | {"name":"Woof","age":10} | object  | null | 0  | null   | $       | $    |
| name | Woof                     | text    | Woof | 2  | 0      | $.name  | $    |
| age  | 10                       | integer | 10   | 4  | 0      | $.age   | $    |
+------+--------------------------+---------+------+----+--------+---------+------+

We can see that each object member has its own row with some useful information, such as its type (SQL text value), path, etc.

Regarding the id column, according to the SQLite documentation this is an internal housekeeping number, the computation of which might change in future releases. The only guarantee is that the id column will be different for every row.

Array

In this example, the JSON value is an array:

SELECT * FROM json_tree('[ 10, 30, 45 ]');

Result:

+------+------------+---------+------+----+--------+---------+------+
| key  |   value    |  type   | atom | id | parent | fullkey | path |
+------+------------+---------+------+----+--------+---------+------+
| null | [10,30,45] | array   | null | 0  | null   | $       | $    |
| 0    | 10         | integer | 10   | 1  | 0      | $[0]    | $    |
| 1    | 30         | integer | 30   | 2  | 0      | $[1]    | $    |
| 2    | 45         | integer | 45   | 3  | 0      | $[2]    | $    |
+------+------------+---------+------+----+--------+---------+------+

Specify a Path

We can use a second argument to specify a path to treat as the top-level.

Example:

SELECT * FROM json_tree('{ "a" : 1, "b" : [ 4, 7, 8 ] }', '$.b');

Result:

+-----+---------+---------+------+----+--------+---------+------+
| key |  value  |  type   | atom | id | parent | fullkey | path |
+-----+---------+---------+------+----+--------+---------+------+
| b   | [4,7,8] | array   | null | 4  | null   | $.b     | $    |
| 0   | 4       | integer | 4    | 5  | 4      | $.b[0]  | $.b  |
| 1   | 7       | integer | 7    | 6  | 4      | $.b[1]  | $.b  |
| 2   | 8       | integer | 8    | 7  | 4      | $.b[2]  | $.b  |
+-----+---------+---------+------+----+--------+---------+------+

Larger Document

In this example we’ll use a larger JSON document. First, let’s call json_tree() without specifying a path:

SELECT * FROM json_tree('[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]'
    );

Result:

+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
|  key   |                            value                             |  type   | atom  |  id  | parent |    fullkey     |    path     |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| null   | [{"user":"Spike","age":30,"scores":[9,7,3]},{"user":"Faye"," | array   | null  | 0    | null   | $              | $           |
| null   | age":25,"scores":[90,87,93]},{"user":"Jet","age":40,"scores  | null    | null  | null | null   | null           | null        |
| null   | ":[50,38,67]}]                                               | null    | null  | null | null   | null           | null        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 0      | {"user":"Spike","age":30,"scores":[9,7,3]}                   | object  | null  | 1    | 0      | $[0]           | $           |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| user   | Spike                                                        | text    | Spike | 3    | 1      | $[0].user      | $[0]        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| age    | 30                                                           | integer | 30    | 5    | 1      | $[0].age       | $[0]        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| scores | [9,7,3]                                                      | array   | null  | 7    | 1      | $[0].scores    | $[0]        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 0      | 9                                                            | integer | 9     | 8    | 7      | $[0].scores[0] | $[0].scores |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 1      | 7                                                            | integer | 7     | 9    | 7      | $[0].scores[1] | $[0].scores |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 2      | 3                                                            | integer | 3     | 10   | 7      | $[0].scores[2] | $[0].scores |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 1      | {"user":"Faye","age":25,"scores":[90,87,93]}                 | object  | null  | 11   | 0      | $[1]           | $           |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| user   | Faye                                                         | text    | Faye  | 13   | 11     | $[1].user      | $[1]        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| age    | 25                                                           | integer | 25    | 15   | 11     | $[1].age       | $[1]        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| scores | [90,87,93]                                                   | array   | null  | 17   | 11     | $[1].scores    | $[1]        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 0      | 90                                                           | integer | 90    | 18   | 17     | $[1].scores[0] | $[1].scores |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 1      | 87                                                           | integer | 87    | 19   | 17     | $[1].scores[1] | $[1].scores |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 2      | 93                                                           | integer | 93    | 20   | 17     | $[1].scores[2] | $[1].scores |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 2      | {"user":"Jet","age":40,"scores":[50,38,67]}                  | object  | null  | 21   | 0      | $[2]           | $           |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| user   | Jet                                                          | text    | Jet   | 23   | 21     | $[2].user      | $[2]        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| age    | 40                                                           | integer | 40    | 25   | 21     | $[2].age       | $[2]        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| scores | [50,38,67]                                                   | array   | null  | 27   | 21     | $[2].scores    | $[2]        |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 0      | 50                                                           | integer | 50    | 28   | 27     | $[2].scores[0] | $[2].scores |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 1      | 38                                                           | integer | 38    | 29   | 27     | $[2].scores[1] | $[2].scores |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+
| 2      | 67                                                           | integer | 67    | 30   | 27     | $[2].scores[2] | $[2].scores |
+--------+--------------------------------------------------------------+---------+-------+------+--------+----------------+-------------+

In this case, our JSON value is an array that contains three objects. Each object is listed in the results, and each of those objects’ members are listed, along with their respective values, and so on.

Now, let’s call json_tree() again, but this time we’ll specify a path:

SELECT * FROM json_tree('[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]',
        '$[1]'
    );

Result:

+--------+----------------------------------------------+---------+------+----+--------+----------------+-------------+
|  key   |                    value                     |  type   | atom | id | parent |    fullkey     |    path     |
+--------+----------------------------------------------+---------+------+----+--------+----------------+-------------+
| null   | {"user":"Faye","age":25,"scores":[90,87,93]} | object  | null | 11 | null   | $[0]           | $           |
| user   | Faye                                         | text    | Faye | 13 | 11     | $[0].user      | $[0]        |
| age    | 25                                           | integer | 25   | 15 | 11     | $[0].age       | $[0]        |
| scores | [90,87,93]                                   | array   | null | 17 | 11     | $[0].scores    | $[0]        |
| 0      | 90                                           | integer | 90   | 18 | 17     | $[0].scores[0] | $[0].scores |
| 1      | 87                                           | integer | 87   | 19 | 17     | $[0].scores[1] | $[0].scores |
| 2      | 93                                           | integer | 93   | 20 | 17     | $[0].scores[2] | $[0].scores |
+--------+----------------------------------------------+---------+------+----+--------+----------------+-------------+

In this case I chose the second array element by specifying [1] (arrays are zero based in SQLite).

The result is that the tree is limited to the second array element only.

Let’s go deeper:

SELECT * FROM json_tree('[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]',
        '$[1].scores'
    );

Result:

+--------+------------+---------+------+----+--------+----------------+-------------+
|  key   |   value    |  type   | atom | id | parent |    fullkey     |    path     |
+--------+------------+---------+------+----+--------+----------------+-------------+
| scores | [90,87,93] | array   | null | 17 | null   | $[0].scores    | $[0]        |
| 0      | 90         | integer | 90   | 18 | 17     | $[0].scores[0] | $[0].scores |
| 1      | 87         | integer | 87   | 19 | 17     | $[0].scores[1] | $[0].scores |
| 2      | 93         | integer | 93   | 20 | 17     | $[0].scores[2] | $[0].scores |
+--------+------------+---------+------+----+--------+----------------+-------------+

The deeper we go, the less rows are returned. That’s because we’re only getting a tree that starts at the specific path that we specified.

Filtering the Query

We can modify our query to filter the results based on a given criteria. For example:

SELECT 
    fullkey, 
    value 
FROM json_tree('[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]'
    )
WHERE json_tree.value LIKE '%Faye%';

Result:

+-----------+--------------------------------------------------------------+
|  fullkey  |                            value                             |
+-----------+--------------------------------------------------------------+
| $         | [{"user":"Spike","age":30,"scores":[9,7,3]},{"user":"Faye"," |
| null      | age":25,"scores":[90,87,93]},{"user":"Jet","age":40,"scores  |
| null      | ":[50,38,67]}]                                               |
+-----------+--------------------------------------------------------------+
| $[1]      | {"user":"Faye","age":25,"scores":[90,87,93]}                 |
+-----------+--------------------------------------------------------------+
| $[1].user | Faye                                                         |
+-----------+--------------------------------------------------------------+

We can return just the last row by doing this:

SELECT *
FROM json_tree('[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]'
    )
WHERE json_tree.value = 'Faye';

Result:

+------+-------+------+------+----+--------+-----------+------+
| key  | value | type | atom | id | parent |  fullkey  | path |
+------+-------+------+------+----+--------+-----------+------+
| user | Faye  | text | Faye | 13 | 11     | $[1].user | $[1] |
+------+-------+------+------+----+--------+-----------+------+

Return Leaf Nodes

We can limit the results to just leaf nodes if we want. By “leaf nodes”, I mean only those keys that have no further child elements.

There are a couple of ways to do this.

One option is to filter out all objects and arrays:

SELECT * 
FROM json_tree('[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]'
    )
WHERE json_tree.type NOT IN ('object','array');

Result:

+------+-------+---------+-------+----+--------+----------------+-------------+
| key  | value |  type   | atom  | id | parent |    fullkey     |    path     |
+------+-------+---------+-------+----+--------+----------------+-------------+
| user | Spike | text    | Spike | 3  | 1      | $[0].user      | $[0]        |
| age  | 30    | integer | 30    | 5  | 1      | $[0].age       | $[0]        |
| 0    | 9     | integer | 9     | 8  | 7      | $[0].scores[0] | $[0].scores |
| 1    | 7     | integer | 7     | 9  | 7      | $[0].scores[1] | $[0].scores |
| 2    | 3     | integer | 3     | 10 | 7      | $[0].scores[2] | $[0].scores |
| user | Faye  | text    | Faye  | 13 | 11     | $[1].user      | $[1]        |
| age  | 25    | integer | 25    | 15 | 11     | $[1].age       | $[1]        |
| 0    | 90    | integer | 90    | 18 | 17     | $[1].scores[0] | $[1].scores |
| 1    | 87    | integer | 87    | 19 | 17     | $[1].scores[1] | $[1].scores |
| 2    | 93    | integer | 93    | 20 | 17     | $[1].scores[2] | $[1].scores |
| user | Jet   | text    | Jet   | 23 | 21     | $[2].user      | $[2]        |
| age  | 40    | integer | 40    | 25 | 21     | $[2].age       | $[2]        |
| 0    | 50    | integer | 50    | 28 | 27     | $[2].scores[0] | $[2].scores |
| 1    | 38    | integer | 38    | 29 | 27     | $[2].scores[1] | $[2].scores |
| 2    | 67    | integer | 67    | 30 | 27     | $[2].scores[2] | $[2].scores |
+------+-------+---------+-------+----+--------+----------------+-------------+

Another way to do it is to check the atom column for null values. Specifically, we’d only return rows where this column doesn't contain a null value:

SELECT * 
FROM json_tree('[
        { 
        "user" : "Spike",
        "age" : 30,
        "scores" : [ 9, 7, 3 ]
        },
        { 
        "user" : "Faye",
        "age" : 25,
        "scores" : [ 90, 87, 93 ]
        },
        { 
        "user" : "Jet",
        "age" : 40,
        "scores" : [ 50, 38, 67 ]
        }
        ]'
    )
WHERE atom IS NOT NULL;

Result:

+------+-------+---------+-------+----+--------+----------------+-------------+
| key  | value |  type   | atom  | id | parent |    fullkey     |    path     |
+------+-------+---------+-------+----+--------+----------------+-------------+
| user | Spike | text    | Spike | 3  | 1      | $[0].user      | $[0]        |
| age  | 30    | integer | 30    | 5  | 1      | $[0].age       | $[0]        |
| 0    | 9     | integer | 9     | 8  | 7      | $[0].scores[0] | $[0].scores |
| 1    | 7     | integer | 7     | 9  | 7      | $[0].scores[1] | $[0].scores |
| 2    | 3     | integer | 3     | 10 | 7      | $[0].scores[2] | $[0].scores |
| user | Faye  | text    | Faye  | 13 | 11     | $[1].user      | $[1]        |
| age  | 25    | integer | 25    | 15 | 11     | $[1].age       | $[1]        |
| 0    | 90    | integer | 90    | 18 | 17     | $[1].scores[0] | $[1].scores |
| 1    | 87    | integer | 87    | 19 | 17     | $[1].scores[1] | $[1].scores |
| 2    | 93    | integer | 93    | 20 | 17     | $[1].scores[2] | $[1].scores |
| user | Jet   | text    | Jet   | 23 | 21     | $[2].user      | $[2]        |
| age  | 40    | integer | 40    | 25 | 21     | $[2].age       | $[2]        |
| 0    | 50    | integer | 50    | 28 | 27     | $[2].scores[0] | $[2].scores |
| 1    | 38    | integer | 38    | 29 | 27     | $[2].scores[1] | $[2].scores |
| 2    | 67    | integer | 67    | 30 | 27     | $[2].scores[2] | $[2].scores |
+------+-------+---------+-------+----+--------+----------------+-------------+

The atom column is the SQL value corresponding to primitive elements – elements other than JSON arrays and objects. This column is always null for a JSON array or object. The value column is the same as the atom column for primitive JSON elements but takes on the text JSON value for arrays and objects.

A Database Example

Suppose we have the following table:

SELECT * FROM scores;

Result:

+---------+---------------------------------------------------------+
| teacher |                        students                         |
+---------+---------------------------------------------------------+
| Zohan   | [{"Amy":[10,8,9]},{"Josh":[3,2,4]},{"Igor":[7,6,5]}]    |
| Stacy   | [{"Pete":[5,3,1]},{"Liz":[5,8,7]},{"Jet":[9,5,7]}]      |
| Aisha   | [{"Zolton":[4,6,7]},{"Bree":[7,7,4]},{"Rohit":[9,8,8]}] |
+---------+---------------------------------------------------------+

This table called scores has two columns. The first column contains the name of the teacher, and the second column contains a JSON document. The JSON document contains students and their scores.

Here’s an example of running a query that uses json_tree() against this table:

SELECT
    teacher,
    key AS student,
    value
FROM 
    scores, 
    json_tree(students, '$[1].Liz')
WHERE json_tree.key = 'Liz';

Result:

+---------+---------+---------+
| teacher | student |  value  |
+---------+---------+---------+
| Stacy   | Liz     | [5,8,7] |
+---------+---------+---------+

Here, we returned all scores for Liz, and we also returned her teacher.

The following query returns her second score:

SELECT
    teacher,
    key AS student,
    value AS score
FROM 
    scores, 
    json_tree(students, '$[1].Liz')
WHERE json_tree.key = 1;

Result:

+---------+---------+-------+
| teacher | student | score |
+---------+---------+-------+
| Stacy   | 1       | 8     |
+---------+---------+-------+