SQLite json_each() Function

The SQLite json_each() function walks the specified JSON document, generates a row for each top child element, and returns a result set consisting of all the rows.

The SQLite json_each() function only processes the immediate children of a JSON object or array, or the element itself if the element is a primitive type.

The SQLite json_each() function is a table-valued function, and json_tree() is a similar function.

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 value of the current element.
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: If value is a primitive JSON value, atom store its corresponding SQL value; otherwise, the column is NULL.
id: An integer identifying the uniqueness of this row.
parent: The integer ID of the parent element. This column is always NULL.
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 some examples to show the usages of json_each().

Example: Array

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

SELECT * FROM json_each('[1, 2, 3]');

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

Example: Object

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

SELECT * FROM json_each('{"x": 1, "y": 2}');

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

Example: Specify Path

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

SELECT * FROM json_each('[{"x": 1, "y": 2}]', '$[0]');

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