SQLite json_each() Function

The SQLite json_each() table-valued function returns one row for each immediate child of a top-level JSON object or array. If the selected value is a primitive, it returns one row for that value.

Unlike json_tree(), json_each() does not recurse into nested objects or arrays. Use it when you need one-level rows; use json_tree() to walk nested JSON recursively.

SQLite includes JSON functions by default starting with version 3.38.0; older builds may need the JSON1 extension enabled. Starting with version 3.45.0, json_each() also accepts SQLite’s JSONB input format. See the SQLite JSON documentation.

Syntax

Here is the syntax of the SQLite json_each() function:

json_each(json, path)

Parameters

json: Required. A JSON document.
path: Optional. The path expression.

Return value

The SQLite json_each() function returns a result set with the following columns:

key: If the JSON is an array, the key column is the index of the array; if the JSON is an object, the key column is the member name of the object; otherwise, the key is NULL.
value: The current JSON value as an SQLite value. Objects and arrays are returned as JSON text.
type: The JSON type of the current element. Possible values: 'null', 'true', 'false', 'integer', 'real', 'text', 'array', 'object'. They are the same as the return values of json_type().
atom: The corresponding SQLite value for primitive JSON elements. It is NULL for arrays and objects; JSON null also maps to SQL NULL, so check type to distinguish it.
id: An internal integer that differs for each row in the result. Its calculation can change between SQLite releases, so do not store it as a persistent identifier.
parent: Always NULL for json_each(). Use json_tree() when you need parent IDs for nested rows.
fullkey: It is the path to the current row element.
path: The path to the parent element of the current row element.

Examples

Here are examples that show how to use json_each().

Example: Array

In this example, use the json_each() function to iterate over the elements in a JSON array:

SELECT key, value, type, atom, fullkey, path
FROM json_each('[1, 2, 3]');

key  value  type     atom  fullkey  path
---  -----  -------  ----  -------  ----
0    1      integer  1     $[0]     $
1    2      integer  2     $[1]     $
2    3      integer  3     $[2]     $

Example: Object

In this example, use the json_each() function to iterate over the elements in a JSON object:

SELECT key, value, type, atom, fullkey, path
FROM json_each('{"x": 1, "y": 2}');

key  value  type     atom  fullkey  path
---  -----  -------  ----  -------  ----
x    1      integer  1     $.x      $
y    2      integer  2     $.y      $

Example: Specify Path

In this example, use the json_each() function to iterate over the elements selected by a path in a JSON array:

SELECT key, value, type, atom, fullkey, path
FROM json_each('[{"x": 1, "y": 2}]', '$[0]');

key  value  type     atom  fullkey  path
---  -----  -------  ----  -------  ------
x    1      integer  1     $[0].x   $[0]
y    2      integer  2     $[0].y   $[0]