SQL One-Liner: Aggregate Order IDs into a JSON Array per Customer Instantly! #SQL #DataScience #JSON

Turning relational rows into JSON arrays is essential for modern applications and APIs. The long way uses a correlated subquery with FOR XML PATH (SQL Server) or manual concatenation to assemble a quoted, comma-separated list and then wrap it in brackets—clever but verbose and harder to maintain.

Modern SQL dialects provide built-in JSON aggregation functions—JSON_AGG in PostgreSQL and JSON_ARRAYAGG in MySQL—that collapse entire groups into JSON arrays in a single, declarative function call. Even in SQL Server, leveraging FOR JSON PATH with WITHOUT_ARRAY_WRAPPER and STRING_AGG yields the same result succinctly. These one-liners not only reduce boilerplate code but also improve performance by pushing the work into optimized native JSON routines, ensuring your data is ready for consumption in JSON-centric environments.

Queries:

✅ Long Way (Using a Subquery and STRING_AGG / FOR JSON PATH):

SELECT
'[' +
STUFF((
FROM orders o
FOR XML PATH('')), 1, 1, ''
) + ']' AS orders_json
FROM (
SELECT DISTINCT customer_id
FROM orders
) AS c;

Explanation:

We first select each distinct customer_id from the orders table.

For each customer, the correlated subquery concatenates all their order_id values with a leading comma (via FOR XML PATH) and wraps each in quotes (QUOTENAME).

STUFF(...,1,1,'') removes the leading comma.

Finally we wrap the result in square brackets [...] to form a valid JSON array string.

✅ Shortcut One-Liner (Using JSON_AGG / STRING_AGG):

-- PostgreSQL
SELECT
customer_id,
JSON_AGG(order_id) AS orders_json
FROM orders
GROUP BY customer_id;

-- MySQL 8.0+
SELECT
customer_id,
JSON_ARRAYAGG(order_id) AS orders_json
FROM orders
GROUP BY customer_id;

-- SQL Server 2017+
SELECT
customer_id,
(SELECT STRING_AGG(CONVERT(varchar(20), order_id), ',')
FROM orders o
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS orders_json
FROM (
SELECT DISTINCT customer_id
FROM orders
) AS c;

Explanation:

PostgreSQL: JSON_AGG(order_id) automatically builds a JSON array of order_id values per group.

MySQL: JSON_ARRAYAGG(order_id) does the same in MySQL 8.0+.

SQL Server: A concise FOR JSON PATH with STRING_AGG and WITHOUT_ARRAY_WRAPPER can produce a JSON array in one subquery.