SQL Server解析MS Graph SecureScores JSON遇返回0行及查询优化问题

第一个查询返回0行的原因 #

你的第一个查询失败，是因为存储在jsonValue字段中的顶层JSON是一个数组（而非单个对象）。直接使用JSON_QUERY(jsonValue, '$.controlScores')会试图从顶层对象中查找controlScores属性，但顶层结构是数组，自然匹配不到任何内容，所以返回0行。

获取controlScores数组的正确方式 #

方式1：单个查询直接提取controlScores内容 #

先展开顶层数组，再针对每个数组元素提取controlScores：

SELECT x.*
FROM Odata_json AS c
CROSS APPLY OPENJSON(jsonValue) AS topLevel
CROSS APPLY OPENJSON(JSON_QUERY(topLevel.[value], '$.controlScores')) 
WITH (
    controlName nvarchar(100) '$.controlName', 
    description nvarchar(1000) '$.description'
) AS x 
WHERE oID = 11

如果确定顶层数组中只有一个SecureScore对象，也可以简化为直接指定数组索引：

SELECT x.*
FROM Odata_json AS c
CROSS APPLY OPENJSON(JSON_QUERY(jsonValue, '$[0].controlScores')) 
WITH (
    controlName nvarchar(100) '$.controlName', 
    description nvarchar(1000) '$.description'
) AS x 
WHERE oID = 11

方式2：分开查询 #

先提取主数据，再单独提取controlScores：

提取主数据

SELECT 
    JSON_VALUE(topLevel.[value], '$.activeUserCount') AS activeUserCount,
    JSON_VALUE(topLevel.[value], '$.createdDateTime') AS createdDateTime,
    JSON_VALUE(topLevel.[value], '$.currentScore') AS currentScore,
    JSON_VALUE(topLevel.[value], '$.enabledServices') AS enabledServices,
    JSON_VALUE(topLevel.[value], '$.licensedUserCount') AS licensedUserCount,
    JSON_VALUE(topLevel.[value], '$.maxScore') AS maxScore,
    JSON_VALUE(topLevel.[value], '$.id') AS id,
    JSON_VALUE(topLevel.[value], '$.azureTenantId') AS azureTenantId,
    JSON_VALUE(topLevel.[value], '$.deviceScore') AS deviceScore,
    JSON_VALUE(topLevel.[value], '$.dataScore') AS dataScore,
    JSON_VALUE(topLevel.[value], '$.identityScore') AS identityScore
FROM Odata_json AS c
CROSS APPLY OPENJSON(jsonValue) AS topLevel
WHERE oID = 11

单独提取controlScores

SELECT 
    c.oID,
    x.*
FROM Odata_json AS c
CROSS APPLY OPENJSON(jsonValue) AS topLevel
CROSS APPLY OPENJSON(JSON_QUERY(topLevel.[value], '$.controlScores')) 
WITH (
    controlName nvarchar(100) '$.controlName', 
    description nvarchar(1000) '$.description'
) AS x 
WHERE oID = 11

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