Flatten Nested JSON Array in SQL

Problem

See More

Flatten Nested JSON Array in SQL.

An e-commerce checkout service exports each order as a single row where all purchased items are packed into one delimited items column (for example Laptop|Mouse|Keyboard), the way an upstream API once serialized a JSON array. The analytics team needs this in relational form: one row per individual item. Flatten the items column by splitting it on the | separator, producing one output row per item that carries the order's order_id and customer_id, the item's 1-based position within the order as item_position, and the item text as item_name. Sort the result by order_id ascending, then by item_position ascending.

Schema columns: orders.order_id, orders.customer_id, orders.items

Output columns: order_id, customer_id, item_position, item_name

Examples

Example 1

Input:

orders:

order_id	customer_id	items
1	101	Laptop|Mouse|Keyboard
2	102	Monitor
3	103	USB|HDMI|VGA
4	101	Laptop|Screen
5	102	Mouse|Keyboard|USB|Monitor
6	104	Headphones

Output:

order_id	customer_id	item_position	item_name
1	101	1	Laptop
1	101	2	Mouse
1	101	3	Keyboard
2	102	1	Monitor
3	103	1	USB
3	103	2	HDMI
3	103	3	VGA
4	101	1	Laptop

The output above is truncated; the input table also shows only a sample of the rows, and the full result (22 rows) reflects the full dataset.

Explanation: Order 1 contains three pipe-delimited items, so it expands to three rows with positions 1 through 3 in the order they appear in the string. Order 2 has a single item and produces exactly one row with item_position 1. Every output row repeats the parent order's order_id and customer_id.

Constraints

• Split items on the | character; positions are 1-based and follow the left-to-right order of items in the string.
• Sort by order_id ascending, then item_position ascending.
• Result columns must be named exactly: order_id, customer_id, item_position, item_name.
• item_position must be an integer.
• Single-item orders produce exactly one row with item_position = 1.