A JSON API response, in SQL in SQL

One query, one document: the shape the caller wanted, assembled where the data lives.

WITH item_docs AS (
    SELECT
        i.order_id,
        JSON_OBJECT(
            'sku', i.sku,
            'name', p.name,
            'quantity', i.quantity,
            'line_total', ROUND(i.quantity * p.price, 2)
        ) AS item
    FROM order_items AS i
    JOIN products AS p
        ON p.sku = i.sku
),
order_docs AS (
    SELECT
        o.customer_id,
        JSON_OBJECT(
            'id', o.id,
            'status', o.status,
            'total', o.total,
            'placed_at', o.placed_at,
            'items', JSON_GROUP_ARRAY(JSON(d.item))
        ) AS document
    FROM orders AS o
    JOIN item_docs AS d
        ON d.order_id = o.id
    GROUP BY o.id
)
SELECT
    JSON_OBJECT(
        'generated_at', DATETIME('now'),
        'customers', JSON_GROUP_ARRAY(
            JSON_OBJECT(
                'customer_id', customer_id,
                'orders', JSON(document)
            )
        )
    ) AS response
FROM order_docs;

How it works

Build the leaf objects in a CTE, then aggregate them one level up.
json_group_array inside json_object nests an array in a record.
The result is a single row and a single column the caller can return as is.

Keywords and builtins used here

AS
BY
FROM
GROUP
JOIN
ON
SELECT
WITH

The run, in numbers

Lines: 39
Characters to type: 661
Tokens: 168
Three-star pace: 95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 106 seconds.

Type this snippet

Step 5 of 5 in Writing JSON, step 8 of 14 in JSON & search.

← Previous Next →