SQLite 3 reference · JSON Functions
SQLite JSON_EACH() Function
A table-valued function that returns one row per element of a JSON array or object, with key, value and type columns.
SQLite 3 Since 3.9.0
Syntax
SELECT ... FROM json_each(json, path)
Parameters
| Parameter | Description |
|---|---|
json |
JSON text (or JSONB). |
path optional |
Walk the array or object at this path. |
Return value
Rows with key, value, type, atom, id, parent, fullkey and path columns.
Examples
SELECT key, value, type
FROM json_each('{"a":1,"b":"two","c":[3]}');
Output
key | value | type a | 1 | integer b | two | text c | [3] | array
WITH posts(id, tags) AS (VALUES (1, '["sql","json"]'), (2, '["sql"]'))
SELECT tag.value AS tag, count(*) AS posts
FROM posts, json_each(posts.tags) AS tag
GROUP BY tag.value;
Output
tag | posts json | 1 sql | 2
Version history
| Version | Change |
|---|---|
| SQLite 3.9.0 | Added |
| SQLite 3.38.0 | Built in by default; earlier versions needed the JSON1 extension. |
In other languages
JSONB_EACH()· PostgreSQLJSONB_ARRAY_ELEMENTS()· PostgreSQLUNNEST()· PostgreSQL
Related
JSON_TREE()· A table-valued function like json_each() that walks a JSON value recursively, returning a row for every nested element.JSON_EXTRACT()· Read one or more values from JSON by path. JSON objects and arrays come back as text; numbers and strings as SQL values.JSON_ARRAY_LENGTH()· Return the number of elements in a JSON array, optionally at a path. Non-arrays give 0.- SQL String Functions · tutorial