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_id | product |
|---|---|
| 1 | apple |
| 1 | banana |
| 2 | milk |
Write a query that returns each order_id alongside a JSON array of
all its products.
Expected output (columns: order_id, products), ordered byorder_id:
1|["apple","banana"]
2|["milk"]Hint —
json_group_array(expr)is an aggregate function, just like
SUMorCOUNT— use it withGROUP 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\"]"