Azure SQL查询特殊结构JSON无返回结果问题求助

失效的核心原因是有问题的JSON最外层是数组结构（用[]包裹），而正常JSON是单个对象。原查询用OPENJSON(doc, '$.value')直接查找根对象下的value字段，但外层是数组时，根节点是数组，不存在$.value这个路径，自然返回空结果。

先遍历外层数组的每个元素，再提取每个元素内部value数组里的数据：

SELECT jsond.*
FROM OPENROWSET (
        bulk 'adls/test/test.json',
        data_source = 'datalake',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b'
    ) 
    WITH (doc nvarchar(max)) AS ROWS
    -- 遍历外层数组，获取每个数组元素
    CROSS APPLY OPENJSON(doc) AS outerArr
    -- 解析每个元素内的value数组
    CROSS APPLY OPENJSON(outerArr.value, '$.value')
        WITH (
            [Id] INT,
            [Year] INT,
            [Name] NVARCHAR(200)
        ) AS jsond

如果要同时支持单个对象和外层数组两种格式，可通过判断根节点类型统一处理：

SELECT jsond.*
FROM OPENROWSET (
        bulk 'adls/test/test.json',
        data_source = 'datalake',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b'
    ) 
    WITH (doc nvarchar(max)) AS ROWS
    -- 判断根节点类型，统一转为数组遍历逻辑
    CROSS APPLY (
        CASE WHEN ISJSON(doc) = 1 AND JSON_VALUE(doc, '$') LIKE '[%' THEN
            OPENJSON(doc)
        ELSE
            -- 单个对象包装成数组，对齐数组处理逻辑
            OPENJSON(CONCAT('[', doc, ']'))
        END
    ) AS outerArr
    CROSS APPLY OPENJSON(outerArr.value, '$.value')
        WITH (
            [Id] INT,
            [Year] INT,
            [Name] NVARCHAR(200)
        ) AS jsond

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