codejump
MediumPro challengeSQL

Aggregate Rows into a JSON Array

SQLDatabasesJSON

This is the reverse of exploding a JSON array: given normal relational
rows, build one JSON array per group.

Table: `order_items`

order_idproduct
1apple
1banana
2milk

Write a query that returns each order_id alongside a JSON array of
all its products.

Expected output (columns: order_id, products), ordered by
order_id:

1|["apple","banana"]
2|["milk"]

Hint — json_group_array(expr) is an aggregate function, just like

SUM or COUNT — use it with GROUP BY.

Sample tests

Test #1Two orders, one with multiple products
Input: "CREATE TABLE order_items (order_id INTEGER, product TEXT);\nINSERT INTO order_items VALUES\n (1, 'apple'), (1, 'banana'), (2, 'milk');"
Output: "1|[\"apple\",\"banana\"]\n2|[\"milk\"]"
Test #2Single product, single-element array
Input: "CREATE TABLE order_items (order_id INTEGER, product TEXT);\nINSERT INTO order_items VALUES\n (1, 'only');"
Output: "1|[\"only\"]"
← All challenges