⚡ SQL One-Liner: Flatten Nested JSON Arrays in SQL Server Instantly! #SQL #DataScience #JSON

Working with complex JSON structures in SQL Server used to require verbose, multi‑step queries involving nested OPENJSON … WITH calls to break apart JSON objects and arrays

In the long‑way solution, we parsed the JSON into separate columns, then used two levels of CROSS APPLY OPENJSON to expand both the parent shipmentStops array and the child orderReferenceNumbers array into individual rows

This approach reduces query complexity, eliminates intermediate JSON columns, and executes in a single, efficient pass over the JSON data—perfect for real‑time analyses, ETL tasks, and reporting pipelines

By mastering these techniques—JSON_VALUE(), OPENJSON() with explicit paths, and CROSS APPLY—you can transform deeply nested JSON into relational rows with minimal code, improving both readability and performance

Queries:

✅ Long Way (Using Multiple CROSS APPLY and WITH Clauses):

DECLARE @json NVARCHAR(MAX) = N'{
"shipmentDetails": { "shipmentId": "JHVJD5627278788" },
"shipmentStops": [
{ "stopSequence": 1, "orderReferenceNumbers": ["2120549020", "test"] },
{ "stopSequence": 2, "orderReferenceNumbers": ["2120549020", "2120549002"] }
]
};

SELECT
WITH (
shipmentDetails NVARCHAR(MAX) AS JSON,
shipmentStops NVARCHAR(MAX) AS JSON
) AS d
WITH (shipmentId NVARCHAR(20) '$.shipmentId') AS sd
WITH (
stopSequence INT,
orderReferenceNumbers NVARCHAR(MAX) AS JSON
) AS ss

Here, we parse top‑level JSON into two JSON columns (shipmentDetails, shipmentStops) using OPENJSON … WITH

We then expand shipmentDetails to extract the shipmentId field

Next, we expand the shipmentStops array into rows and extract both stopSequence and the nested JSON array orderReferenceNumbers

Finally, we unnest the orderReferenceNumbers array into individual rows via a second CROSS APPLY OPENJSON

✅ True Shortcut One-Liner:

SELECT

We directly extract shipmentId using JSON_VALUE() without a separate OPENJSON call