如何在SQL Server中扁平化JSON嵌套数组

我尝试使用SQL代码在SQL Server中扁平化JSON嵌套数组，但未取得成功。

原始JSON数据 #

{
    "shipmentDetails": {
        "shipmentId": "JHVJD5627278788"
    },
    "shipmentStops": [
        {
            "stopSequence": 1,
            "orderReferenceNumbers": [
                "2120549020", "test"
            ]
        },
        {
            "stopSequence": 2,
            "orderReferenceNumbers": [
                "2120549020", "2120549002"
            ]
        }
    ]
}

原始SQL代码 #

DECLARE @Step AS NVARCHAR(max) = N'Variables declaration';
DECLARE @JSON1 AS NVARCHAR(MAX);

SET @JSON1 = '{
    "shipmentDetails": {
        "shipmentId": "JHVJD5627278788"
    },
    "shipmentStops": [
        {
            "stopSequence": 1,
            "orderReferenceNumbers": [
                "2120549020", "test"
            ]
        },
        {
            "stopSequence": 2,
            "orderReferenceNumbers": [
                "2120549020", "2120549002"
            ]
        }
    ]
}'

IF OBJECT_ID('JSONPO2') IS NOT NULL
    DROP TABLE JSONPO2

SET @Step = N'JSON data parsing and loading into JSONPO2 temp table'

SELECT DISTINCT ShipDetails.shipmentId AS shipmentId
    ,ShipmentStops.stopSequence AS stopSequence
    ,ShipmentStops.orderReferenceNumbers AS orderReferenceNumbers
INTO JSONPO2
FROM OPENJSON(@JSON1) WITH (
        shipmentDetails NVARCHAR(MAX) AS JSON
        ,shipmentStops NVARCHAR(MAX) AS JSON
        ) AS [Data]
CROSS APPLY OPENJSON(shipmentDetails) WITH (shipmentId NVARCHAR(20)) AS ShipDetails
CROSS APPLY OPENJSON(shipmentStops) WITH (
        stopSequence INT
        ,orderReferenceNumbers NVARCHAR(MAX) AS JSON
        ) AS ShipmentStops
CROSS APPLY OPENJSON(orderReferenceNumbers) WITH (orderReferenceNumbers VARCHAR(max)) 
AS orderReferenceNumbers

SELECT *
FROM JSONPO2

当前执行结果 #

执行上述代码后仅得到2行结果，orderReferenceNumbers列显示完整数组：

期望结果 #

需要解析嵌套数组，得到如下4行结果：

修改方案 #

问题出在三个地方：

1. 最后一步解析orderReferenceNumbers数组时，无需用WITH子句指定列，简单字符串数组直接用OPENJSON即可返回每个元素的value；
2. SELECT语句错误引用了未解析的ShipmentStops.orderReferenceNumbers数组，应改用最后一层CROSS APPLY返回的元素值；
3. 多余的DISTINCT会合并结果，需要移除。

修改后的SQL代码 #

DECLARE @Step AS NVARCHAR(max) = N'Variables declaration';
DECLARE @JSON1 AS NVARCHAR(MAX);

SET @JSON1 = '{
    "shipmentDetails": {
        "shipmentId": "JHVJD5627278788"
    },
    "shipmentStops": [
        {
            "stopSequence": 1,
            "orderReferenceNumbers": [
                "2120549020", "test"
            ]
        },
        {
            "stopSequence": 2,
            "orderReferenceNumbers": [
                "2120549020", "2120549002"
            ]
        }
    ]
}'

IF OBJECT_ID('JSONPO2') IS NOT NULL
    DROP TABLE JSONPO2

SET @Step = N'JSON data parsing and loading into JSONPO2 temp table'

SELECT ShipDetails.shipmentId AS shipmentId
    ,ShipmentStops.stopSequence AS stopSequence
    ,orderRef.value AS orderReferenceNumbers
INTO JSONPO2
FROM OPENJSON(@JSON1) WITH (
        shipmentDetails NVARCHAR(MAX) AS JSON
        ,shipmentStops NVARCHAR(MAX) AS JSON
        ) AS [Data]
CROSS APPLY OPENJSON(shipmentDetails) WITH (shipmentId NVARCHAR(20)) AS ShipDetails
CROSS APPLY OPENJSON(shipmentStops) WITH (
        stopSequence INT
        ,orderReferenceNumbers NVARCHAR(MAX) AS JSON
        ) AS ShipmentStops
CROSS APPLY OPENJSON(ShipmentStops.orderReferenceNumbers) AS orderRef

SELECT *
FROM JSONPO2

验证结果 #

执行修改后的代码，将得到期望的4行结果，每个orderReferenceNumbers元素单独成一行。

内容的提问来源于stack exchange，提问作者adam_szmyt