使用CROSS APPLY与OPENJSON提取嵌套JSON数据遇问题求助

问题根源 #

你的JSON最外层是双层嵌套数组（[[...]]），之前的查询没正确处理这两层数组，根本没定位到包含Staff、Location的业务对象层级，所以才返回null或无结果。

正确提取SQL #

DECLARE @JSON VARCHAR(MAX)
SELECT @JSON = BulkColumn
FROM OPENROWSET(BULK 'D:\pathtojsonfile\staff_location_test_data.json', SINGLE_CLOB) import

SELECT
    -- 提取Staff模块字段
    s.StaffKey,
    s.Id AS StaffId,
    s.LastName,
    -- 提取Location模块字段
    l.CompanyKey AS LocationCompanyKey,
    l.Id AS LocationId,
    l.Address,
    -- 提取ExtendedCredentialingFields下LocationDetails的字段
    ld.Name AS LocationDetailName,
    ld.AddressLine1,
    ld.City,
    ld.State,
    ld.Zip
FROM 
    -- 处理最外层第一层数组
    OPENJSON(@JSON) AS outer_array
    -- 拆解第二层数组，拿到实际业务对象
    CROSS APPLY OPENJSON(outer_array.value) AS inner_array
    -- 解析Staff嵌套对象
    CROSS APPLY OPENJSON(inner_array.value, '$.Staff')
    WITH (
        StaffKey NVARCHAR(100),
        Id NVARCHAR(25),
        LastName NVARCHAR(100)
    ) AS s
    -- 解析Location嵌套对象
    CROSS APPLY OPENJSON(inner_array.value, '$.Location')
    WITH (
        CompanyKey NVARCHAR(100),
        Id NVARCHAR(25),
        Address NVARCHAR(200)
    ) AS l
    -- 解析ExtendedCredentialingFields下的LocationDetails嵌套对象
    CROSS APPLY OPENJSON(inner_array.value, '$.ExtendedCredentialingFields.LocationDetails')
    WITH (
        Name NVARCHAR(100),
        AddressLine1 NVARCHAR(100),
        City NVARCHAR(50),
        State NVARCHAR(10),
        Zip NVARCHAR(20)
    ) AS ld

代码逻辑说明 #

• 先通过OPENJSON(@JSON)处理最外层的第一层数组，得到第二层数组元素；
• 再用CROSS APPLY OPENJSON(outer_array.value)拆解第二层数组，拿到真正存储业务数据的对象；
• 最后针对每个嵌套对象（Staff、Location、LocationDetails），用CROSS APPLY结合OPENJSON指定路径（如'$.Staff'）提取字段，给重复字段加别名是为了避免命名冲突。

执行上述查询后，就能获取你需要的所有字段值，不会再出现null或无结果的情况。

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