Building JSON Output With JSON_OBJECT and JSON_ARRAYAGG

Sooner or later an API wants a nested document and your data is in three tables. MySQL can assemble the document in the query, so the application receives one JSON value per order. The functions are simple, and the traps are not on the page: an aggregate that cannot be ordered, booleans that come out as numbers, an empty result that is NULL, and a join that quietly multiplies the elements. This article builds an order document from relational tables and goes through each of them with the output of a MySQL 8.4 run.

The building blocks

• JSON_OBJECT(k1, v1, k2, v2, ...) makes an object. The keys must be strings (or convertible), and an odd number of arguments is an error: json_object('a') raised Incorrect parameter count in the call to native function 'json_object'.

• JSON_ARRAY(a, b, ...) makes an array: json_array(1, 'a', null, true, now() is not null) gave [1, "a", null, true, true].

• JSON_ARRAYAGG(expr) aggregates one value per row into an array, and JSON_OBJECTAGG(key, value) into an object.

Values keep their natural JSON form: integers and decimals are numbers (price 20.00 stayed 20.00), strings are strings, dates and times become ISO-like strings ("2026-01-04"), NULL becomes null, and a value that is already JSON is embedded as JSON and not quoted. A very large integer is fine: 12345678901234567890 came through unchanged, though a JavaScript client reading it will round it (how JSON handles big numbers).

Booleans come out as numbers

MySQL has no real boolean column type. BOOLEAN is an alias for TINYINT(1), and json_object('paid', paid) printed "paid": 1. The literal true and a comparison expression print true, though: in one row I got {"cmp": true, "bool_lit": true, "paid_col": 1, "if_true": 1}, where cmp was paid = 1. An IF(paid, true, false) also printed 1, because it produces an integer. If the consumer needs real booleans, write the comparison:

json_object('paid', paid = 1)

which prints true or false. This is a well-known source of bugs in JavaScript clients that check paid === true.

A nested order document

The usual job: an order, its customer, and its items as an array.

select json_object(
         'id',       o.id,
         'placed',   o.placed,
         'paid',     o.paid = 1,
         'customer', json_object('id', c.id, 'name', c.name),
         'items',    (select coalesce(json_arrayagg(json_object('sku', i.sku, 'qty', i.qty, 'price', i.price)),
                                      json_array())
                      from c_items i where i.order_id = o.id)
       ) as doc
from c_orders o
join c_customers c on c.id = o.customer_id
where o.id = 3;

The result for order 3 had the keys id, paid, items, placed and customer, in the sorted order MySQL stores them, with four item objects. The key points of the query are the same as in any database: the customer is one row per order, so it is built in the parent query, and the items are many per order, so they are aggregated in a subquery that only touches c_items. And coalesce(..., json_array()) makes an order with no items show [] instead of null, since JSON_ARRAYAGG over zero rows returns SQL NULL: json_arrayagg(sku) for an order id that does not exist returned NULL.

Ordering and distinct are missing

PostgreSQL lets you write jsonb_agg(x order by y). MySQL's JSON_ARRAYAGG does not: both of these were syntax errors in 8.4.11:

select json_arrayagg(sku order by id) from c_items where order_id = 1;   -- error 1064
select json_arrayagg(distinct sku) from c_items where order_id = 1;       -- error 1064

The common workaround is a derived table that sorts first:

select json_arrayagg(sku)
from (select sku from c_items where order_id = 1 order by price desc) s;

It returned the SKUs in the sorted order in my test, but MySQL does not promise that an aggregate keeps the order of a derived table, and an optimizer change can break it without warning. If order is part of the meaning of the array, build the array in the application from sorted rows, or aggregate with a window function and verify the result on your version. For distinct, select distinct in the derived table.

A window form also exists, json_arrayagg(sku) over (partition by order_id), which returns the array on every row of the partition. It is useful when you need the array beside the row data, and not for building one document per group.

Empty, NULL and duplicates

• NULL values are kept: json_arrayagg(sku) over a NULL and 'a' gave [null, "a"].

• A NULL key is an error: json_objectagg(k, v) with a NULL k raised ERROR 3158: JSON documents may not contain NULL member names, and so did json_object(null, 1). Filter such rows or coalesce the key.

• Duplicate keys are silent. json_object('a', 1, 'a', 2) gave {"a": 2}, the last value. json_objectagg('a', 1) and ('a', 2) in a group gave {"a": 2} too. Group before you aggregate if collisions are possible.

• A number as a key becomes a string: json_object(1, 2) is {"1": 2}.

• Nesting aggregates is not allowed: json_arrayagg(json_arrayagg(1)) raised Invalid use of group function. Aggregate in a subquery, then aggregate that.

The fan-out bug

Joining a parent to two child tables and then aggregating gives every element once for each row of the other table. I joined c_items to itself to stand in for two child relationships of four rows each:

select o.id, json_arrayagg(i.sku) a, json_arrayagg(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;

Both arrays came back with 16 elements, a with each SKU four times in a row and b cycling through all four four times. There is no error, and totals are inflated by the same factor. The fix is to aggregate each child relationship in its own subquery, as in the document above, or to join already-aggregated derived tables. It is the same trap as in PostgreSQL (aggregating rows into JSON with jsonb_agg), and in both, the symptom is plausible-looking output.

Speed

I built a document per order, 20,000 in all, in two ways:

join + group by (json_arrayagg of the joined rows)              179 ms
correlated subquery in the select list                           211 ms

The correlated subquery ran 20,000 index lookups on order_id, each returning four rows, and the join form read the items once. Both are in the same range, and on larger sets the join is better as long as there is only one child relationship. For several children, the subquery per relationship stays the cleanest, and its cost is one index lookup per parent per child.

GROUP_CONCAT is not a substitute

Before JSON aggregates, people built arrays with concat('[', group_concat(json_object(...) separator ','), ']'). It works on small results and breaks on large ones, because group_concat_max_len defaults to 1,024 bytes and truncation is silent. On the 80,000 items, json_valid(concat('[', group_concat(json_object('sku', sku) separator ','), ']')) returned 0: the string had been cut mid-object. json_arrayagg over all 80,000 rows produced a 3.3 MB document without complaint, limited by max_allowed_packet (64 MB here). Use JSON_ARRAYAGG and leave group_concat for text.

Taking the document to the application

A document built in SQL is one value, and it is sent over the wire as text. Most drivers hand it to you as a string or an already parsed value. If the only thing the application does is forward it to a browser, pass the string through untouched. Documents larger than a few megabytes are better streamed as JSON Lines or paginated, since the whole document must be built in memory on the server and on the client (JSON Lines for data pipelines and log files). To compare two versions of a document, the JSON diff tool runs in the browser.

The MySQL manual lists the functions under creating JSON values and aggregate functions. For reading such documents back, see using the JSON column type in MySQL.

When the application should build the JSON

The shape changes with the client. If a mobile app and a web app need different fields, the serializer in application code is easier to test and version than SQL strings.

You need ordered arrays. Without ORDER BY inside the aggregate, the guarantee is weak. Build from sorted rows in code.

The result is large. One huge JSON_ARRAYAGG ties up memory and may hit max_allowed_packet. Page the rows.

Types matter. If the client needs booleans, nulls versus absent keys, and numbers kept exact, an explicit serializer with a schema removes the guesswork that TINYINT(1) and decimal bring.

You are building the JSON to store it back. If the nested document is going into a JSON column for reads, think about whether it should be a view or a materialized table instead, so it does not go stale.