SQLite 3 reference · JSON Functions
SQLite JSON_TREE() Function
A table-valued function like json_each() that walks a JSON value recursively, returning a row for every nested element.
SQLite 3 Since 3.9.0
Syntax
SELECT ... FROM json_tree(json, path)
Parameters
| Parameter | Description |
|---|---|
json |
JSON text (or JSONB). |
path optional |
Start from this element. |
Return value
Rows with key, value, type, fullkey, path and more.
Examples
SELECT fullkey, type, atom
FROM json_tree('{"user":{"name":"Ada","langs":["en","fr"]}}');
Output
fullkey | type | atom $ | object | NULL $.user | object | NULL $.user.name | text | Ada $.user.langs | array | NULL $.user.langs[0] | text | en $.user.langs[1] | text | fr
SELECT fullkey, atom
FROM json_tree('{"a":{"b":1,"c":{"d":2}}}')
WHERE atom IS NOT NULL;
Output
fullkey | atom $.a.b | 1 $.a.c.d | 2
Version history
| Version | Change |
|---|---|
| SQLite 3.9.0 | Added |
| SQLite 3.38.0 | Built in by default; earlier versions needed the JSON1 extension. |
Related
JSON_EACH()· A table-valued function that returns one row per element of a JSON array or object, with key, value and type columns.- SQL String Functions · tutorial