SQL Server解析REST API返回的嵌套JSON仅返回第一行如何解决

问题原因 #

目标JSON为三级嵌套结构，包含data、coordinates、dates三层数组，原查询没有逐层展开嵌套数组，也未将嵌套数组字段标记为JSON类型，因此只能读取到第一层数据。

修正后的查询代码 #

你只需要将原有代码中最后的SELECT查询部分替换为以下内容即可：

SELECT 
    Data_Level.parameter,
    Coord_Level.lat,
    Coord_Level.lon,
    Date_Level.date,
    Date_Level.value
FROM OPENJSON((SELECT Json_Table FROM @json))
WITH (
    [data] NVARCHAR(MAX) AS JSON
) AS MetaData
-- 展开第一层data数组
CROSS APPLY OPENJSON(MetaData.data)
WITH(
    [parameter] NVARCHAR(MAX),
    [coordinates] NVARCHAR(MAX) AS JSON
) AS Data_Level
-- 展开第二层coordinates数组
CROSS APPLY OPENJSON(Data_Level.coordinates)
WITH(
    [lat] FLOAT,
    [lon] FLOAT,
    [dates] NVARCHAR(MAX) AS JSON
) AS Coord_Level
-- 展开第三层dates数组
CROSS APPLY OPENJSON(Coord_Level.dates)
WITH(
    [date] DATETIME,
    [value] FLOAT
) AS Date_Level

关键说明 #

• 所有嵌套数组类型的字段，在WITH子句中必须加上AS JSON标记，才能被后续的OPENJSON正常解析
• 三层数组分别用CROSS APPLY逐层展开后，即可获取JSON中所有的lat、lon、date、value记录
• 若需要写入正式SQL Server表，将上述SELECT语句改为INSERT INTO 目标表(parameter,lat,lon,date,value)加后续查询逻辑即可

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