SQLite jsonb_tree() Function

The SQLite jsonb_tree() table-valued function works like json_tree(): it returns the selected JSON value and recursively returns its descendants as rows. Its difference is that value contains JSONB for objects and arrays instead of JSON text.

jsonb_tree() was added in SQLite 3.51.0. SQLite JSONB is an internal SQLite format; it is not compatible with PostgreSQL JSONB and should not be treated as an application-defined binary format. See the SQLite JSON documentation.

For storage choices, conversion, and compatibility details, see SQLite JSONB: When and How to Use It.

Syntax

jsonb_tree(json, path)

json is a JSON or JSONB value. The optional path selects the value from which recursive traversal begins. The function returns the same columns as json_tree(): key, value, type, atom, id, parent, fullkey, and path.

key is the array index or object key; it is NULL for the top-level row.
value is an SQLite scalar for primitive values and JSONB for objects and arrays.
type identifies the JSON value type. Use it to distinguish JSON null from SQL NULL in atom.
atom contains the SQLite value for primitive elements and is NULL for objects and arrays.
id is an internal row number that can change between SQLite releases; do not persist it as a stable identifier.
parent is the id of a parent row, or NULL for the traversal’s top-level row.
fullkey and path identify the element path and its containing path.

Example: inspect nested JSONB values

This query returns the selected root and descendants. The type filter selects only objects and arrays, whose value is returned as a BLOB in SQLite JSONB format. json(value) renders that value as JSON text:

SELECT fullkey, type, typeof(value) AS storage_class, json(value) AS value_json
FROM jsonb_tree('{"order":{"items":[{"sku":"A1"}, {"sku":"B2"}]}}')
WHERE type IN ('object', 'array');

fullkey              type    storage_class  value_json
-------------------  ------  -------------  -----------------------------------
$                    object  blob           {"order":{"items":[{"sku":"A1"},{"sku":"B2"}]}}
$.order              object  blob           {"items":[{"sku":"A1"},{"sku":"B2"}]}
$.order.items        array   blob           [{"sku":"A1"},{"sku":"B2"}]
$.order.items[0]     object  blob           {"sku":"A1"}
$.order.items[1]     object  blob           {"sku":"B2"}

For one-level traversal, use jsonb_each(). For recursive rows whose object and array values are returned as text JSON, use json_tree().

Syntax

Example: inspect nested JSONB values

On this page