Expanding JSON arrays into rows using SQLite's json_each: Aggregating order details by product

About this article
This article was created using an automated generation workflow powered by generative AI. Official SQLite JSON function specifications were reviewed, and sample data consists of fictitious orders.

Verification: Official specifications checked / Execution on real SQLite hardware not confirmed
Information verification date: October 11, 2026. The following outputs are expected examples.

When a JSON array is stored in a single cell, standard SQL makes it difficult to aggregate data by product. By using SQLite's json_each(), you can expand each element of the array into virtual rows and aggregate them using SUM() or GROUP BY. There is no need to update the source data.

Here, we consider a scenario where the items column of the orders table contains an array of product names, quantities, and unit prices. We will extract line items per order, calculate sales by product, and examine cases involving malformed JSON or empty arrays.

Why expand JSON arrays into rows

For example, when a single order contains two products, the JSON looks like this.

[
  {"sku":"pen","qty":2,"price":120},
  {"sku":"note","qty":1,"price":350}
]

While this structure is easy for humans to read, as standard SQL columns, sku and qty are not independent. Splitting strings by commas is vulnerable to JSON quotes, escapes, and nesting, and should be avoided.

json_each(items) is a table-valued function that returns elements directly beneath a JSON array one by one. If each element is a JSON object, the returned value for json_extract(value, '$.sku'), you can extract the required values as shown in.

flowchart LR
    A["orders: 1行にitems配列"] --> B["json_each(items)"]
    B --> C["商品明細: 1要素=1行"]
    C --> D["json_extractでsku/qty/price取得"]
    D --> E["GROUP BYとSUMで集計"]

This transformation is performed only during the SELECT operation. It does not decompose and resave the original JSON or table contents.

Preparation: Creating dummy orders

In SQLite 3.38.0 and later, JSON functions are built-in by default. However, configurations specifying SQLITE_OMIT_JSON at build time are an exception. The following is SQL executed in the SQLite command-line client.

CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    items TEXT NOT NULL
);

INSERT INTO orders (order_id, items) VALUES
(101, '[{"sku":"pen","qty":2,"price":120},{"sku":"note","qty":1,"price":350}]'),
(102, '[{"sku":"pen","qty":3,"price":120}]'),
(103, '[]');

TEMP TABLE is used to avoid mixing practice data into existing production tables. Please run this in a new, empty SQLite database. The table will disappear when the connection is closed.

Order 101 contains two pens and one notebook, order 102 contains three pens, and order 103 contains no items. Quantities and unit prices are integers, and taxes, discounts, and returns are not handled.

First, expand and observe a single record

SELECT
    o.order_id,
    j.key AS array_index,
    json_extract(j.value, '$.sku') AS sku,
    json_extract(j.value, '$.qty') AS qty,
    json_extract(j.value, '$.price') AS price
FROM orders AS o
JOIN json_each(o.items) AS j
WHERE o.order_id = 101
ORDER BY j.key;

The expected result is that two rows are returned from order 101. The array indices key will be 0 and 1, and sku will be pen and note, respectively.json_each() iterates over the elements of the array, and json_extract() extracts values from the specified path within each element.

Here, JOIN serves to expand the JSON contained in each row of orders into a set of elements corresponding to that row. In SQL, json_each(o.items) references the left-hand o.items.

Order 103 with an empty JSON array,json_each() does not appear in the results of this JOIN because it returns no elements.If you want to retain orders that have no items, a LEFT JOIN described later is required.

Aggregating sales by product

Multiply the quantity and unit price of the expanded elements and calculate their sum for each product code.

SELECT
    json_extract(j.value, '$.sku') AS sku,
    SUM(
        json_extract(j.value, '$.qty') *
        json_extract(j.value, '$.price')
    ) AS sales
FROM orders AS o
JOIN json_each(o.items) AS j
GROUP BY json_extract(j.value, '$.sku')
ORDER BY sku;

The expected result is note = 350、pen = 600. Pens have a total of 5 units (2 from order 101 and 3 from order 102), and at a unit price of 120, the total is 600.

Here, it is assumed that quantities and unit prices are numeric. Real-world JSON may contain strings like "qty":"2", null, missing fields, negative numbers, and other anomalies. While SQLite has type conversion rules, relying on implicit conversion for business financial calculations is risky.

In practice, you should first determine conditions such as type checking like json_type(j.value,'$.qty')='integer', whether quantities are greater than or equal to zero, and whether unit prices are within an acceptable range. To avoid rounding errors with decimal amounts, also consider a design that handles the smallest currency unit as an integer.

Retaining orders with empty arrays in the list

When checking counts per order, removing empty arrays makes it impossible to distinguish whether an order did not exist or had zero items.

SELECT
    o.order_id,
    COUNT(j.key) AS item_count
FROM orders AS o
LEFT JOIN json_each(o.items) AS j
GROUP BY o.order_id
ORDER BY o.order_id;

The expected result is 2 for order 101, 1 for 102, and 0 for 103.LEFT JOIN retains the left-hand side order even if there are no matching elements.COUNT(*)If you use , even an empty array would be counted as 1 due to the row generated by LEFT JOIN, so here we use COUNT(j.key).

In addition,item_count is the number of product type elements.qty This is different from the sum of quantities. Order 101 has 2 line items, but a total quantity of 3. Please distinguish which one you are looking for.

Handling malformed JSON

The JSON function may throw an error if the input is invalid JSON. Avoid blindly passing strings received from external systems to json_each().

First, verify using read-only mode.

SELECT order_id, json_valid(items) AS valid_json
FROM orders
ORDER BY order_id;

All three cases here are expected to be 1.json_valid() can be used to validate normal JSON strings. However, even with valid JSON, the top level is not necessarily an array.

If you want to expand only arrays, one approach is to normalize the input before passing it to the function as follows.

SELECT
    o.order_id,
    j.key,
    j.value
FROM orders AS o
JOIN json_each(
    CASE
        WHEN json_valid(o.items) AND json_type(o.items) = 'array'
        THEN o.items
        ELSE '[]'
    END
) AS j;

Because invalid JSON and non-array values are treated as empty arrays, aggregation processing can continue. However, this does not mean invalid data should be treated as normal data. In business operations, you need to separately extract invalid records, investigate the cause, and record the number of items excluded from processing.

What is the difference between json_each and json_tree?

json_each() returns the elements directly under the specified array or object. It is suitable for use cases such as processing "each product in the array" as done here. On the other hand, json_tree() recursively traverses descendants, making it useful when you want to search for values deep inside nested structures.

If you simply expand deep hierarchies using json_tree(), it will also include the object's own rows and intermediate nodes. For processing that counts only product rows, extra rows may be mixed in. First, determine the necessary hierarchy, and if immediate children are sufficient, choose json_each().

json_extract() You can retrieve values at specific positions like $[0].sku using just the path. However, if the array length changes, hardcoding indices like 0 and 1 becomes difficult to maintain. If the array length is variable, json_each() is more natural.

Operational considerations and architectural decisions

Storing JSON in columns is not inherently bad.It is useful for flexible attributes received from external APIs or occasionally referenced auxiliary data. However, if order items are frequently aggregated and subjected to search criteria or integrity constraints, normalizing the items into a separate table may make the design more manageable.

Unpacking JSON every time incurs parsing costs. For large datasets, consider maintaining searchable fields as regular or generated columns, using indexes, or separating them into item tables. Actual performance depends on the row count, JSON size, SQL queries, and SQLite version, soEXPLAIN QUERY PLAN and determine based on actual measurements.

The SQL queries used here are read-only if they are SELECT statements, but the practiceCREATE TEMP TABLE and INSERT create temporary tables. Do not practice on a production database; please test on an empty verification database.

Finally, add INSERT INTO orders VALUES (104, '[{"sku":"note","qty":2,"price":350}]'); and aggregate sales by product once again. The notebook sales should change from 350 to 1050. Verifying this change yourself helps clarify the workflow of converting JSON arrays into rows before aggregation.

Official documentation

• SQLite JSON Functions And Operators — json_each, json_extract, json_valid, json_tree, and built-in conditions

• SQLite SELECT — Basics of JOIN, GROUP BY, and aggregation

• SQLite Aggregate Functions — Behavior of COUNT and SUM

The SQL queries in this article are instructional materials based on the official SQLite specification. Execution has not been verified on actual SQLite hardware, so the listed figures are expected values calculated from sample data.