Menu

SQLite jsonb_each() Function

The SQLite jsonb_each() table-valued function works like json_each(): it returns one row for each immediate child of a top-level object or array, or one row for a primitive value. Its difference is that value contains JSONB for child objects and arrays instead of JSON text.

jsonb_each() 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_each(json, path)

json is a JSON or JSONB value. The optional path selects the value whose immediate children should be returned. The function returns the same columns as json_each(): key, value, type, atom, id, parent, fullkey, and path.

  • key is the array index or object key; it is NULL for a primitive top-level value.
  • 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 NULL, as jsonb_each() does not recursively report parent relationships.
  • fullkey and path identify the element path and its containing path.

Example: get nested JSONB values

Use typeof(value) to confirm that child objects and arrays are returned as BLOB values. Call json(value) when you want text JSON for display:

SELECT key, type, typeof(value) AS storage_class, json(value) AS value_json
FROM jsonb_each('{"profile":{"name":"Ada"},"tags":["sql","db"]}')
WHERE type IN ('object', 'array');

key      type    storage_class  value_json
-------  ------  -------------  -------------------
profile  object  blob           {"name":"Ada"}
tags     array   blob           ["sql","db"]

For recursive traversal through nested values, use jsonb_tree(). For text JSON values from child objects and arrays, use json_each().