json_each, json_tree, and Querying JSON Arrays/Objects Relationally

Learning outcomes

An array is convenient inside a document, but SQL works most naturally with rows. SQLite's JSON table-valued functions bridge those models by presenting document elements as a temporary relational rowset that can participate in joins, filters, grouping, and ordering.

Scalar extraction answers one path; traversal answers many elements

If the question is “what is this device's firmware version?”, json_extract() is direct. If the question is “which devices have any tag equal to critical?”, the document contains zero or more candidate elements. json_each() exposes those immediate children as rows.

SELECT p.device_id,       j.key AS array_index,       j.value AS tag,       j.type AS json_typeFROM device_profile AS p,     json_each(p.metadata, '$.tags') AS jORDER BY p.device_id, j.key;

For an array, key is the zero-based array index. For an object, key is the member name.

json_each(): shallow traversal

json_each(X) walks only the immediate children of the top-level array/object. Its optional second argument chooses a path to treat as the traversal root. That makes it ideal for a known tags array or one level of optional attributes.

SELECT DISTINCT d.device_code, d.device_nameFROM device AS dJOIN device_profile AS p ON p.device_id=d.device_idJOIN json_each(p.metadata, '$.tags') AS tagWHERE tag.value = 'critical'ORDER BY d.device_code;

This is a relational join even though one side is produced dynamically from a JSON value. The json_each() rowset exists for the query; it is not a persisted child table or index.

json_tree(): recursive traversal

json_tree() recursively walks nested substructure. Current SQLite exposes descriptive columns including key, value, type, atom, id, parent, fullkey, and path. The id is useful for relating rows within one traversal, but its exact computation is an internal detail and should not be persisted as an application identifier.

Inspect leaves instead of printing every container

Recursive output can be noisy because each object and array also gets a row. Filtering to atom IS NOT NULL gives a compact list of primitive leaf values. If JSON null must remain visible, filter by type NOT IN ('object','array') instead, because a JSON null has a NULL atom.

SELECT d.device_code,       jt.fullkey,       jt.type,       jt.atomFROM device AS dJOIN device_profile AS p ON p.device_id=d.device_idJOIN json_tree(p.metadata) AS jtWHERE jt.type NOT IN ('object','array')ORDER BY d.device_code, jt.fullkey;

Search nested attributes without assuming one fixed depth

Recursive traversal can be useful during data discovery or migration when an attribute may be nested differently across historical payloads. Do not let that convenience become a permanent excuse for an uncontrolled schema.

SELECT DISTINCT d.device_code,       jt.fullkey,       jt.value AS protocolFROM device AS dJOIN device_profile AS p ON p.device_id=d.device_idJOIN json_tree(p.metadata) AS jtWHERE jt.key = 'protocol'  AND jt.type = 'text'ORDER BY d.device_code;

If a production query repeatedly searches “anywhere in the document” for an operational key, that is evidence the data contract may be too vague.

Object traversal is useful for dynamic attribute reports

For a known object such as $.network, json_each() can produce key/value rows that are easy to display in a generic diagnostics UI.

SELECT d.device_code,       attr.key AS attribute_name,       attr.value AS attribute_value,       attr.type AS json_typeFROM device AS dJOIN device_profile AS p ON p.device_id=d.device_idJOIN json_each(p.metadata, '$.network') AS attrORDER BY d.device_code, attr.key;

A generic attribute viewer is a strong JSON use case. A high-volume query that filters millions of rows by one of those attributes is a different workload and may call for indexing or relational promotion.

Performance mental model: traversal work happens at query time

Each call must interpret the document and walk relevant structure. SQLite's newer internal JSONB representation can avoid text parsing, but traversal is still not equivalent to an indexed relational child table. There is no automatic B-tree index over every arbitrary JSON key or array element.

Failure case: mixed plain text and JSON arrays

json_each() expects well-formed JSON. If one historical row stores critical as plain SQL text while another stores ["critical"], a single traversal query can fail or require shape-specific branches. The Chapter 13 schema prevents that by validating every metadata document; individual member shapes still need a documented contract.

SELECT device_id,       json_type(metadata, '$.tags') AS tags_type,       json_array_length(metadata, '$.tags') AS tag_countFROM device_profileORDER BY device_id;

Expected healthy rows report array for tags_type. Missing tags return SQL NULL for the type/path rather than pretending an empty array exists.

Lab: tags and nested-attribute search

-- A. Immediate array membershipSELECT DISTINCT d.device_codeFROM device AS dJOIN device_profile AS p USING(device_id)JOIN json_each(p.metadata,'$.tags') AS tagWHERE tag.value IN ('critical','inspection')ORDER BY d.device_code;-- B. Recursive discovery of text leaves containing "modbus"SELECT DISTINCT d.device_code, jt.fullkey, jt.atomFROM device AS dJOIN device_profile AS p USING(device_id)JOIN json_tree(p.metadata) AS jtWHERE jt.type='text'  AND lower(jt.atom) LIKE '%modbus%'ORDER BY d.device_code, jt.fullkey;

Record the query requirement, not just the result. If the membership search becomes a core application path, compare a normalized device_tag table against repeated JSON traversal before choosing an optimization.

Version note: jsonb_each/jsonb_tree

SQLite 3.51.0 added jsonb_each() and jsonb_tree(). They differ mainly in returning JSONB for object/array values in the value column. Chapter 13 does not require them because json_each()/json_tree() have much broader version compatibility and already teach the relational traversal model.

Verification checkpoint

Production judgment and bridge

Traversal makes nested JSON queryable, but parsing and representation still have costs. Lesson 4 introduces SQLite JSONB as an optional internal representation and measures it without confusing it with PostgreSQL JSONB or promising universal speedups.

Authoritative references

• SQLite JSON table-valued functions
• SQLite JSON Functions and Operators
• SQLite Query Planner documentation