SQL Reference ยท JSON and Special Features
SQL json_each() and json_tree() functions
Reference for json_each() and json_tree() in SQLite: turn JSON arrays and objects into rows, the key/value/type/fullkey columns, and other databases' equivalents.
json_each() and json_tree() are table-valued functions: you use them in FROM like a table, and they return one row per element of a JSON array or per member of a JSON object. json_each() looks only at the top level (or at the path you give it); json_tree() walks every nested level.
This is how you query inside JSON arrays: filter on array contents, count tags, or join array elements to other tables.
SELECT key, value, type FROM json_each('["red", "green"]'); -- 0 red text -- 1 green text
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Mei has an empty array, so she produces no rows. To keep events without tags, use LEFT JOIN json_each(...) ON 1.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
json_each(json [, path]) json_tree(json [, path])
| Column | Description |
|---|---|
key |
Array index (from 0) or object member name. |
value |
The element as an SQL value (text, number) or JSON text for objects and arrays. |
type |
null, true, false, integer, real, text, array or object. |
atom |
The SQL value for scalars; NULL for arrays and objects. |
id |
A unique row number for the element within the document. |
parent |
The id of the containing element (NULL at the top). |
fullkey |
The full path of the element, such as $.user.langs[1]. |
path |
The path of the container of the element. |
path, if given, starts the walk at that location instead of at $. json_each() on a scalar returns one row for it. Invalid JSON raises an error.
Dialect differences
| Database | Array to rows | Notes |
|---|---|---|
| SQLite | json_each, json_tree |
Comma join or CROSS JOIN works because the function reads the outer row. |
| PostgreSQL | jsonb_array_elements(arr), jsonb_each(obj), jsonb_array_elements_text |
Use CROSS JOIN LATERAL or a comma in FROM. |
| MySQL | JSON_TABLE(doc, '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')) |
8.0+; very flexible, with typed columns. |
| SQL Server | OPENJSON(doc, '$.tags') |
Returns key, value, type; CROSS APPLY joins it to rows. |
| Oracle | JSON_TABLE(doc, '$.tags[*]' COLUMNS (tag PATH '$')) |
12c+. |
-- PostgreSQL SELECT e.id, t.tag FROM events e CROSS JOIN LATERAL jsonb_array_elements_text(e.payload -> 'tags') AS t(tag); -- MySQL SELECT e.id, t.tag FROM events e, JSON_TABLE(e.payload, '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')) AS t; -- SQL Server SELECT e.id, t.value AS tag FROM events e CROSS APPLY OPENJSON(e.payload, '$.tags') AS t;
Usage notes
- Empty or missing arrays give no rows. Use an outer join to keep the parent row.
- Strings come back unquoted in
valueandatom, so you can compare them directly:WHERE t.value = 'promo'. - Aggregate afterward with
GROUP BYto count orjson_group_array()to rebuild arrays. - Normalize if you query it often. Searching inside arrays scans every document; a child table with an index is faster.
json_tree()is for exploration. It is handy for discovering the shape of an unfamiliar document.- Storage: do not store lists as JSON when you will join on them frequently.