Expanding JSON Arrays With json_each and json_tree

JSON in a single column becomes useful for reporting the moment it turns into rows. SQLite's tool for that is json_each, a table-valued function that takes a document and returns one row per element, and its recursive sibling json_tree. They do what jsonb_array_elements does in PostgreSQL and JSON_TABLE does in MySQL, with fewer options and a very simple model. This article explains every column they return, shows the joins that go with them, and lists the edge cases: what happens with a scalar, an empty array, a NULL, a path that does not exist.

json_each, column by column

json_each(doc [, path]) produces rows for the direct children of the value at the path (the root, by default). On an object with five keys:

select key, value, type, atom, id, parent, fullkey, path
from json_each('{"a":1,"b":[10,20],"c":{"d":"x"},"e":null,"f":true}');

key  value        type     atom  id  parent  fullkey  path
a    1            integer  1     2           $.a      $
b    [10,20]      array          6           $.b      $
c    {"d":"x"}    object         15          $.c      $
e                 null           22          $.e      $
f    1            true     1     25          $.f      $

What each column means:

• **key** is the object key, or the zero-based position for an array.

• **value** is the element. Scalars become SQL values (an integer is an integer, a string is text, true is 1, JSON null is SQL NULL). Objects and arrays come out as JSON text.

• **type** is the JSON type: integer, real, text, true, false, null, array, object. It is what you filter on when elements are mixed.

• **atom** is the scalar value, and NULL for arrays and objects. It exists to make "give me only the leaves" a one-word filter.

• **id is an internal node number and parent** the parent's node number. parent is only filled in by json_tree; in json_each it is NULL.

• **fullkey** is the path from the document root to the element, such as $.b or $[3]. Keys that are not plain identifiers are quoted ($."in_stock"), so a fullkey is a path you can feed back to json_extract.

• **path** is the path of the container the element sits in: $ for the top-level children, and $.a for the children of $.a.

For an array, key is the index: json_each('[10,"x",[1,2],{"k":1},null]') returned keys 0 to 4, with value 10, x, [1,2], {"k":1} and an empty value for the null.

The second argument

A path narrows where the expansion starts. json_each('{"a":{"b":[1,2]}}', '$.a.b') returned two rows, 0|1|$.a.b[0] and 1|2|$.a.b[1]. Given a path to an object, json_each(..., '$.a') returned one row for the key b, with path equal to $.a. A path that does not exist gives zero rows, not an error.

Joining it to the source row

The idiom is a comma join: a table and a function that refers to it.

select p.id, e.key, e.value
from products p, json_each(p.attrs, '$.tags') e
where p.id = 1;

returned 1|0|sale and 1|1|eco. The function is evaluated once per row of products, with that row's attrs. This is the same lateral behavior as in the other databases, with no keyword needed in SQLite. Aggregates over the expansion are ordinary SQL:

select e.value as tag, count(*) n
from products p, json_each(p.attrs, '$.tags') e
group by 1 order by 2 desc, 1;

tag     n
eco     100001
bulk    100000
new     99999
sale    33334
gift    33333
import  33333

Six tags across 200,000 products in a single pass. Filtering on a set of values is a join: with a table wanted(tag) holding gift and eco, exists (select 1 from json_each(p.attrs, '$.tags') e join wanted w on w.tag = e.value) counted 133,334 products, the same as count(distinct p.id) ... where e.value in (select tag from wanted).

Edge cases that drop rows

A comma join is an inner join, so any source row for which the function returns nothing vanishes. When does it return nothing?

The scalar case is the odd one. json_each('5') returned a single row with an empty key and the value 5, so it treats the scalar as a one-element container. A column that sometimes holds an array and sometimes a single value will expand both without error, with the scalar row's key empty.

To keep source rows with nothing to expand, use a left join: from products p left join json_each(p.attrs, '$.tags') e returns a row with NULL columns for a product without tags. Remember that where e.value = 'gift' then turns the left join back into an inner join.

An invalid document raises malformed JSON and stops the whole query. If a column might contain bad text, add a check (json_valid(attrs)) constraint so that bad text never gets into the column.

Types and comparisons

Because value is an SQL value, comparisons behave as they do for any SQLite value. On [1,"1",true], value = 1 was true for the integer and for true (which is the integer 1) and false for the string "1". Check type when elements are mixed: where e.type = 'integer' is stricter than a comparison on value. A count by type, select typeof(value), type, count(*) ... group by 1, 2, is a quick audit of an array whose contents you do not trust: in the audit I ran on [1,1.5,"x",true,false,null,[1],{"a":1}], true and false both had typeof integer, and the array and the object both had typeof text, so only type distinguishes them from strings.

json_tree: the whole document

json_tree(doc [, path]) returns the node at the path and every descendant, recursively, depth first. On {"a":1,"b":[10,{"c":null}]}:

id  parent  key  type     atom  fullkey      path
0           NULL object          $            $
2   0       a    integer  1     $.a          $
6   0       b    array          $.b          $
9   6       0    integer  10    $.b[0]       $.b
12  6       1    object         $.b[1]       $.b
13  12      c    null           $.b[1].c     $.b[1]

Here the first row is the root itself, parent links each node to its container, and fullkey is unique per node. Uses that come up:

• Leaves only: where atom is not null. On a product document (14 nodes, counting the root) the filter returned 10 leaf values.

• Depth: counting the dots in fullkey gave a deepest path of 3 levels for that product.

• Find a key anywhere: exists (select 1 from json_tree(attrs) where key = 'color' and value = 'red') found 50,000 products without naming the path $.specs.color.

• Discover the shape of unknown documents: select distinct t.fullkey from products p, json_tree(p.attrs) t where p.id <= 100 listed $.brand, $.price, $.sku, $.specs.color, $.specs.dims.h, $.specs.dims.w, $.tags[0], $.tags[1] and the rest, which is how you get oriented in a JSON column you inherited. Add order by 1 and exclude arrays with type != 'array' as needed.

json_tree visits every node of every document, so it costs in proportion to the size of the data. Use json_each with a path when you know where to look.

Cost

Expanding is cheaper than extracting the same fields repeatedly when you need many of them. Summing the two dimensions w and h of 200,000 products took 110 ms with json_each(p.attrs, '$.specs.dims') and 158 ms with two json_extract calls. On a JSONB column the time for the tag-exists query fell from 145 ms to 78 ms. These calls cannot use an index, since they run on the stored text for every row. For lookups by tag, put tags in a table with an index on (tag, product_id) and keep the JSON for display. The same advice applies in PostgreSQL and MySQL.

For the other functions in the family see JSON functions in SQLite and, for reading single values, querying JSON text with json_extract. A document can be inspected in the browser with the JSON formatter before you decide on a path.

When json_each is the wrong tool

You want a single value. A path in json_extract or an arrow is shorter than a lateral join.

The query runs constantly on a big table. Expansion happens at read time, for every document. Materialize the rows into a table, once, with insert into ... select ... from json_each(...).

You need typed columns from each element. json_each gives value and type and nothing more. Several fields per element means several ->> calls on e.value: e.value ->> '$.sku', e.value ->> '$.qty'. That works, and for wide elements it is clumsy compared with JSON_TABLE.

You need to preserve empty arrays. Use a left join, or the inner join silently drops them.

The input may be invalid. One malformed document aborts the query.

The functions are described in the SQLite manual under json_each and json_tree, where the column definitions above are listed in full, and the JSON data model they walk is the one in RFC 8259.