Building JSON Results With json_group_array in SQLite

SQLite builds JSON from query results with two aggregates and two constructors. json_group_array(x) gathers one value per row into an array, json_group_object(k, v) gathers pairs into an object, json_object shapes a row into an object and json_array shapes values into an array. They compose into a nested document in a few lines. They also come with one trap that is specific to SQLite and not obvious from the documentation: a JSON result is marked as JSON only until it passes through a subquery, after which it is just a string. This article builds an order document from three tables, shows the trap with outputs, and covers the rest of the aggregates' behavior.

The pieces

select json_group_array(id) from products where id <= 5;
-- [1,2,3,4,5]
select json_group_object(name, id) from products where id <= 3;
-- {"Product 1":1,"Product 2":2,"Product 3":3}

The output is minified, and values keep their SQL type: integers as numbers, text as strings, NULL as null, reals as numbers (1.50 became 1.5). Booleans do not exist, so a comparison like 1 = 1 becomes 1. If a consumer needs true, write json('true') or a case that returns it.

Properties of the aggregates, each checked on 3.54.1:

• Empty input gives an empty array, [], not NULL. That is different from MySQL and PostgreSQL, where an empty aggregate is NULL and you need a coalesce (the MySQL version and the PostgreSQL version show the other behavior).

• **DISTINCT works**: json_group_array(distinct sku) over one order returned ["S2","S3","S4","S5"].

• **FILTER (WHERE ...)** works: json_group_array(sku) filter (where qty > 2) returned only the matching SKUs.

• **ORDER BY inside the call works from 3.44**: json_group_array(id order by id desc) returned [3,2,1]. Before that, the usual trick was to order in a subquery and hope.

• Window use works: json_group_array(id) over (order by id rows between 1 preceding and current row) returned [1], [1,2], [2,3], [3,4], a sliding window of ids.

• A blob is an error: json_group_array(x'4142') raised JSON cannot hold BLOB values. Encode with hex() first, or convert in the application.

• Duplicate keys are not merged in json_group_object: two pairs with the key a gave {"a":1,"a":2}, which most parsers read as a = 2. A NULL key is skipped, and a NULL value becomes null.

A nested document

An order with its customer and its items:

select json_object(
         'id',       o.id,
         'placed',   o.placed,
         'paid',     json(case when o.paid then 'true' else 'false' end),
         'customer', json_object('id', c.id, 'name', c.name),
         'items',    json((select json_group_array(
                              json_object('sku', i.sku, 'qty', i.qty, 'price', i.price) order by i.id)
                           from c_items i where i.order_id = o.id))
       )
from c_orders o
join c_customers c on c.id = o.customer_id
where o.id = 3;

That json( around the subquery is the whole story of the next section. With it, the result for order 3 had "paid": true, a customer object and an items array of four objects. Without it, the items value looked like this:

"items": "[{\"sku\":\"S4\",\"qty\":5,\"price\":0.03},{\"sku\":\"S5\",...}]"

A single string, with every quote escaped.

The lost subtype

SQLite functions that produce JSON mark their result with a hidden "subtype", a tag that tells the next JSON function to treat the text as JSON and embed it as is. The tag lives on the value only while it is passed straight from one function to the next. Anything that stores the value in between, or moves it through a query boundary, drops the tag, and the next function sees an ordinary string.

I tested the places where that happens:

json_object('a', (select json_group_array(1)))                         {"a":"[1]"}
json_object('a', t.arr) from (select json_group_array(1) arr) t         {"a":"[1]"}
with t as (select json_group_array(id) arr ...) select json_object('ids', arr)    {"ids":"[1,2,3]"}
json_array(json_array(1), (select json_group_array(2)))                [[1],"[2]"]
json_group_array(arr) from (select json_group_array(id) arr ... group by id % 2)   ["[2,4]","[1,3]"]

In every one, the inner value became a string. In the fourth, the first element, a direct json_array(1), stayed an array and the second, from a subquery, became a string, in the same expression. And the fix in each case is the same:

json_object('a', json(t.arr))                   {"a":[1]}
json_object('ids', json(arr))                   {"ids":[1,2,3]}
json_group_array(json(arr))                     [[2,4],[1,3]]

json(x) takes text, validates and minifies it, and returns it with the JSON subtype restored. The rule: **whenever a JSON value comes from a subquery, a CTE, a derived table, a column or a CASE, wrap it in json() before putting it inside another JSON constructor.** The same applies to columns: json_group_array(attrs) where attrs holds JSON text returned an array of escaped strings, and json_group_array(json(attrs)) returned an array of real objects. A column is just text to SQLite.

The bug is easy to ship because the output is valid JSON and looks nearly right. A test that parses the output and checks that items is an array catches it. One that compares text does not.

The fan-out bug

As elsewhere, aggregating after joining two one-to-many tables multiplies elements. I joined c_items to itself to stand for two child relationships:

select o.id, json_group_array(i.sku) a, json_group_array(j.sku) b
from c_orders o
join c_items i on i.order_id = o.id
join c_items j on j.order_id = o.id
where o.id = 1 group by o.id;

Each array had 16 entries: a repeated each SKU four times in a row and b cycled through all four SKUs four times. Aggregate each relationship in its own subquery, wrapped in json(), as in the order document above.

Speed

Twenty thousand order documents, three ways:

correlated subquery in the select list (json_group_array per order)     49 ms
join + group by, json_group_array over the joined rows                   44 ms
join + group by, with ORDER BY inside json_group_array                   56 ms

All three finished in about 50 ms, so the choice is about correctness and clarity, not speed. A detail in the output: the first variant, which I wrote without json() around the subquery, produced 3,946,810 characters of text, and the other two 3,266,810. The difference is the backslashes added by the double encoding. That is a quick check for the bug in a large export: the size is larger than it should be.

For the shell, remember that sqlite3 -json does not preserve the structure either (it prints a JSON column as an escaped string), so build the document in SQL and print it in list mode, as described in JSON functions in SQLite.

Grouping into keyed objects

json_group_object(sku, total) over a grouped query returned {"S2":3,"S3":8,"S4":15,...} for the first five orders: the key is the SKU and the value the quantity. Combine it with a subquery that groups first, so keys are unique. Values can be JSON too, again with json().

For a keyed array of arrays, group by the key and aggregate: select attrs ->> '$.brand' b, json_group_array(id) from products where id <= 10 group by b returned Acme|[5,10], Globex|[1,6] and so on.

To take the result into an application, read the column as text and parse it, or pass it straight to a client. A document of several megabytes should be paged instead; see JSON Lines for data pipelines and log files. The JSON formatter pretty prints a result for inspection.

When the query should not build the JSON

The shape varies with the client. Different consumers want different fields; serializers in application code are easier to test.

The result is large. SQLite builds the whole text in memory. A single aggregate over millions of rows is a memory problem that streaming rows (or JSON Lines) avoids.

You need booleans and exact numbers. SQLite has neither; a serializer with a schema removes the guesswork.

The subtype trap is a recurring risk. If many people write these queries, a view that encapsulates the document, with the json() wrappers in one place, is safer than copies of the expression.

The aggregates are documented in the SQLite manual: json_group_array and json_group_object, and the subtype behavior in the section on how JSON functions pass values.