By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE OrderHistory
(
description nvarchar(max)
)
insert into OrderHistory
values (N'{
"@type": "Test",
"OrderId": 6608216,
"deliverySchedule": {
"@DeliveryType": "Test",
"details": [
{
"@type": "Snickers",
"id": 657117,
"candyLimit": {
"@type": "UNRESTRICTED"
}
}
]
}
}');
1 rows affected
SELECT deliverySchedule."@type",
details1."@type",
candyLimitType
FROM OrderHistory OH
CROSS APPLY OPENJSON(OH.description)
WITH ( "@type" VARCHAR(20),
details1 NVARCHAR(MAX) '$.deliverySchedule.details[0]' AS JSON ) deliverySchedule
CROSS APPLY OPENJSON(details1)
WITH ( "@type" VARCHAR(10),
candyLimitType NVARCHAR(MAX) '$.candyLimit."@type"') details1
| @type | @type | candyLimitType |
|---|---|---|
| Test | Snickers | UNRESTRICTED |