Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT key, value, type FROM json_each('["red", "green"]');
-- 0  red    text
-- 1  green  text

Try it

SQL Array elements as rows

Press Run (or Ctrl + Enter) to see the result here.

SQL Joining a table to its own JSON arrays

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.

SQL json_tree walks every level

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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+.
SQL
-- 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 value and atom, so you can compare them directly: WHERE t.value = 'promo'.
  • Aggregate afterward with GROUP BY to count or json_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.