Aggregating rows into JSON in SQL

One document per group, assembled by the database instead of by the caller.

SELECT
    o.id AS order_id,
    JSON_GROUP_ARRAY(
        JSON_OBJECT('sku', i.sku, 'quantity', i.quantity)
    ) AS items,
    JSON_GROUP_OBJECT(i.sku, i.quantity) AS quantity_by_sku
FROM orders AS o
JOIN order_items AS i
    ON i.order_id = o.id
GROUP BY o.id;

How it works

1. json_group_array collects a group's values into a JSON array.
2. json_group_object builds an object from key and value pairs.
3. Nesting json_object inside gives you an array of records.

Keywords and builtins used here

• AS
• BY
• FROM
• GROUP
• JOIN
• ON
• SELECT

The run, in numbers

Lines

10

Characters to type

235

Tokens

61

Three-star pace

95 tpm

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

Type this snippet

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

← Previous Next →