Build alphabetical receipt tag labels
Your task
Return receipt ID and tags_text with its tags sorted alphabetically and joined with a comma and a space. Use an empty string when there are no tags. Sort by receipt ID.
Expand an array into rows before sorting and joining its values into a display label.
Sample data
receipts| id | sold_on | amount | channel | metadata |
|---|---|---|---|---|
| 1 | 2025-01-01 | 36 | ONLINE | {"currency":"EUR","tags":["gift","jazz"]} |
| 2 | 2025-01-16 | 22 | Store | {"currency":"EUR","tags":["indie"]} |
| 3 | 2025-01-31 | 54 | online | {"currency":"EUR","tags":["returning","bundle"]} |
| 4 | 2025-03-04 | 38 | STORE | {"currency":"EUR","tags":[]} |
| 5 | 2025-04-09 | 44 | Online | {"currency":"EUR","tags":["indie"]} |
Expected output
| id | tags_text |
|---|---|
| 1 | gift, jazz |
| 2 | indie |
| 3 | bundle, returning |
| 4 | |
| 5 | indie |
Show a hint
Use a correlated JSON_EACH subquery with ordered GROUP_CONCAT, and COALESCE for an empty array.
Dates, text, and JSON guide
Work with dates as ISO text
The receipt dates use YYYY-MM-DD, so their text order matches calendar order. Use an inclusive start and exclusive end for periods. This query returns receipts in the first quarter, without including April 1.
SELECT id, sold_on, amount
FROM receipts
WHERE sold_on >= '2025-01-01'
AND sold_on < '2025-04-01';Normalize values before comparing them
The receipt channel values vary in capitalization and spacing. TRIM removes surrounding spaces and LOWER handles these ASCII names. Use the same normalization when grouping values that represent the same category.
SELECT LOWER(TRIM(channel)) AS channel, COUNT(*) AS receipts
FROM receipts
GROUP BY LOWER(TRIM(channel));Extract a field or expand an array
JSON_EXTRACT reads a value at a path, such as the currency code. JSON_EACH turns an array into rows with a value column. After expansion, receipt IDs can repeat and a receipt with no tags contributes no rows.
SELECT r.id, JSON_EXTRACT(r.metadata, '$.currency') AS currency,
tag.value AS tag
FROM receipts AS r
CROSS JOIN JSON_EACH(r.metadata, '$.tags') AS tag;Worked solution
SELECT r.id,COALESCE((SELECT GROUP_CONCAT(value,', ' ORDER BY value) FROM JSON_EACH(r.metadata,'$.tags')),'') AS tags_text FROM receipts r ORDER BY r.id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
