Summary: in this tutorial, you will learn how to use the json_tree() function to iterate over elements of a JSON object or array recursively.
Introduction to the SQLite json_tree() function
In SQLite, the json_tree() is a table-valued function that allows you to iterate over elements of a JSON object or array recursively.
Here’s the syntax of the json_tree() function:
json_tree(json_value)Code language: SQL (Structured Query Language) (sql)In this syntax:
json_valueis the JSON object or array that you want to iterate.
The json_tree() function returns a set of rows, each representing an element in the JSON object or array.
If you want to iterate elements of a JSON object or array recursively, which is specified by a path, you can use the following json_tree() function:
json_tree(json_data, path);Code language: SQL (Structured Query Language) (sql)In this syntax:
json_datais the JSON data from which you want to extract a JSON object or array for iterating.pathis the JSON path expression for selecting the JSON object or array in thejson_data.
The json_tree() function returns a result set that includes the following columns:
key: is the key of the object element or the index of the array element.value: is the value of the element.type: is the data type of the value such asinteger,text,real,null, orobject.atom: is the atomic value of the element such as the number, string, ornull.id: is the identifier for the top-level JSON value (useful for joining with otherjson_tree()results).parent: is the identifier of the parent JSON value (useful for hierarchical queries).fullkey: is the full key path to the element.path: is the JSON path to the element.
Because the json_tree() function is a table-valued function, you need to use it in the place that accepts a table such as in the FROM clause of the SELECT statement.
If you don’t do so, you’ll get the following error:
Parse error: no such function: <span style="background-color: initial; font-family: inherit; font-size: inherit; text-wrap: wrap; color: initial;">json_tree</span>Code language: HTML, XML (xml)SQLite json_tree() function examples
Let’s take some examples of using the json_tree() function.
1) Iterating elements of a JSON object
The following example uses the json_tree() function to iterate over the elements of a JSON object recursively:
SELECT * FROM json_tree('{
"name": "John Doe",
"age": 25,
"address": {
"street": "123 Main Street",
"city": "San Jose",
"state": "CA",
"zipcode": "95134"
}
}');Code language: SQL (Structured Query Language) (sql)Output:
key value type atom id parent fullkey path
------- ------------------------------------------------------------ ------- --------------- -- ------ ----------------- ---------
null {"name":"John Doe","age":25,"address":{"street":"123 Main St object null 0 null $ $
reet","city":"San Jose","state":"CA","zipcode":"95134"}}
name John Doe text John Doe 2 0 $.name $
age 25 integer 25 16 0 $.age $
address {"street":"123 Main Street","city":"San Jose","state":"CA"," object null 23 0 $.address $
zipcode":"95134"}
street 123 Main Street text 123 Main Street 33 23 $.address.street $.address
city San Jose text San Jose 57 23 $.address.city $.address
state CA text CA 71 23 $.address.state $.address
zipcode 95134 text 95134 80 23 $.address.zipcode $.addressCode language: SQL (Structured Query Language) (sql)Notice that the json_each() only returns the top-level elements of the JSON object:
SELECT * FROM json_each('{
"name": "John Doe",
"age": 25,
"address": {
"street": "123 Main Street",
"city": "San Jose",
"state": "CA",
"zipcode": "95134"
}
}');Code language: SQL (Structured Query Language) (sql)Output:
key value type atom id parent fullkey path
------- ------------------------------------------------------------ ------- -------- -- ------ --------- ----
name John Doe text John Doe 2 null $.name $
age 25 integer 25 16 null $.age $
address {"street":"123 Main Street","city":"San Jose","state":"CA"," object null 23 null $.address $
zipcode":"95134"}Code language: SQL (Structured Query Language) (sql)2) Iterating elements of a JSON object specified by a path
The following example uses the json_tree() function to iterate over elements of a JSON object specified by a path:
SELECT * FROM json_tree('{
"name": "John Doe",
"age": 25,
"contact": {
"phone": "(408)-111-2222",
"email": "[email protected]",
"address": {
"street": "123 Main Street",
"city": "San Jose",
"state": "CA",
"zipcode": "95134"
}
}
}', '$.contact');Code language: SQL (Structured Query Language) (sql)Output:
key value type atom id parent fullkey path
------- ------------------------------------------------------------ ------ ----------------- --- ------ ------------------------- -----------------
contact {"phone":"(408)-111-2222","email":"[email protected]","addre object null 23 null $.contact $
ss":{"street":"123 Main Street","city":"San Jose","state":"C
A","zipcode":"95134"}}
phone (408)-111-2222 text (408)-111-2222 33 23 $.contact.phone $.contact
email [email protected] text [email protected] 55 23 $.contact.email $.contact
address {"street":"123 Main Street","city":"San Jose","state":"CA"," object null 80 23 $.contact.address $.contact
zipcode":"95134"}
street 123 Main Street text 123 Main Street 90 80 $.contact.address.street $.contact.address
city San Jose text San Jose 114 80 $.contact.address.city $.contact.address
state CA text CA 128 80 $.contact.address.state $.contact.address
zipcode 95134 text 95134 137 80 $.contact.address.zipcode $.contact.addressCode language: SQL (Structured Query Language) (sql)3) Iterating elements of a JSON array
The following example uses the json_tree() function to iterate over the elements of a JSON array recursively:
SELECT * FROM json_tree('[1,2,3,[4,5]]');Code language: SQL (Structured Query Language) (sql)Output:
key value type atom id parent fullkey path
---- ------------- ------- ---- -- ------ ------- ----
null [1,2,3,[4,5]] array null 0 null $ $
0 1 integer 1 1 0 $[0] $
1 2 integer 2 3 0 $[1] $
2 3 integer 3 5 0 $[2] $
3 [4,5] array null 7 0 $[3] $
0 4 integer 4 8 7 $[3][0] $[3]
1 5 integer 5 10 7 $[3][1] $[3]Code language: SQL (Structured Query Language) (sql)Summary
- Use the
json_tree()function to recursively iterate over elements of a JSON object or a JSON array.
