SQLite json_each(): Expand JSON Arrays and Filter Rows
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
keycolumn is the index of the array; if the JSON is an object, thekeycolumn is the member name of the object; otherwise, thekeyisNULL. 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 ofjson_type(). atom- The corresponding SQLite value for primitive JSON elements. It is
NULLfor arrays and objects; JSON null also maps to SQLNULL, so checktypeto 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
NULLforjson_each(). Usejson_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]Filter rows by a value inside a JSON array
When a table stores an array in a JSON text column, join it to json_each() to match individual elements. This example finds products tagged sqlite:
CREATE TABLE products (
id INTEGER PRIMARY KEY,
tags TEXT
);
INSERT INTO products VALUES
(1, '["sqlite","json"]'),
(2, '["mysql"]'),
(3, 'not json'),
(4, NULL);
SELECT p.id, j.value AS tag
FROM products AS p
JOIN json_each(
CASE WHEN json_valid(p.tags) THEN p.tags ELSE '[]' END
) AS j
WHERE j.value = 'sqlite'
ORDER BY p.id;
id tag
-- ------
1 sqliteThe CASE expression passes an empty array for invalid JSON or SQL NULL, so malformed rows do not abort the query. If the column is guaranteed to contain valid JSON, use the simpler JOIN json_each(p.tags) AS j. For validation rules, see json_valid().
Extract fields from an array of JSON objects
Each json_each() row can itself contain an object. Extract its fields with json_extract():
SELECT json_extract(j.value, '$.name') AS name,
json_extract(j.value, '$.qty') AS quantity
FROM json_each(
'[{"name":"pen","qty":2},{"name":"book","qty":1}]'
) AS j;
name quantity
---- --------
pen 2
book 1json_each() expands only the outer array here. To recursively visit nested elements, use json_tree().
Try the SQLite example online
Run and edit the SQL examples from this page in the SQLite Playground. It uses a real SQLite database in your browser, so you can test the query without installing SQLite.