codejump
MediumPro challengeSQL

Explode a JSON Array

SQLDatabasesJSON

Orders store their line items as a JSON array of product names in a single
column, rather than a separate line-items table.

Table: orders

iditems
1["apple","banana","cherry"]
2["milk","bread"]

Write a query that returns one row per item: the order's id next to
each individual item from its items array.

Expected output (columns: id, item), ordered by id then array
position:

1|apple
1|banana
1|cherry
2|milk
2|bread

Hint — json_each(column) is a table-valued function: put it in

the FROM clause (comma-joined with the outer table) and it produces one

row per array element, with .value and .key (the array index) columns.

Sample tests

Test #1Two orders with different item counts
Input: "CREATE TABLE orders (id INTEGER, items TEXT);\nINSERT INTO orders VALUES\n (1, '[\"apple\",\"banana\",\"cherry\"]'),\n (2, '[\"milk\",\"bread\"]');"
Output: "1|apple\n1|banana\n1|cherry\n2|milk\n2|bread"
Test #2Single-item array
Input: "CREATE TABLE orders (id INTEGER, items TEXT);\nINSERT INTO orders VALUES\n (1, '[\"single\"]');"
Output: "1|single"
← All challenges