Build JSON from rows (aggregate rows into a JSON array/object)

The reverse of expanding JSON: collapse many rows into one JSON array of objects (e.g. an order with its line items as JSON). The idea is the same everywhere; only the function names differ.

PostgreSQL / DuckDB

SELECT order_id,
       json_agg(json_build_object('sku', sku, 'qty', qty)) AS items
FROM order_lines
GROUP BY order_id;
-- DuckDB also has json_group_array(); Postgres use jsonb_* for jsonb

MySQL 8+

SELECT order_id,
       JSON_ARRAYAGG(JSON_OBJECT('sku', sku, 'qty', qty)) AS items
FROM order_lines
GROUP BY order_id;

SQLite 3.38+

SELECT order_id,
       json_group_array(json_object('sku', sku, 'qty', qty)) AS items
FROM order_lines
GROUP BY order_id;

BigQuery

SELECT order_id,
       TO_JSON_STRING(ARRAY_AGG(STRUCT(sku, qty))) AS items
FROM order_lines
GROUP BY order_id;

Snowflake

SELECT order_id,
       ARRAY_AGG(OBJECT_CONSTRUCT('sku', sku, 'qty', qty)) AS items
FROM order_lines
GROUP BY order_id;

Ordering inside the array: most dialects accept ORDER BY inside the aggregate — e.g. json_agg(... ORDER BY line_no) (Postgres/DuckDB), JSON_ARRAYAGG(... ) in MySQL ignores order so sort in a subquery first. To read JSON back out, see expand a JSON array into rows.