Azure SQL提取JSON列中ReferenceId与Options值返回NULL问题求助

根因说明 #

• JSON_VALUE 函数仅支持提取JSON结构中的单个标量值（字符串、数字、布尔值等），无法直接返回数组或对象类型的结果
• 你的JSON结构中References、Resources均为数组类型，Resources下的Options字段也是数组，你写的查询路径未指定数组索引，也没有对数组结构做拆解，因此返回NULL

正确实现代码 #

提取所有ReferenceId值 #

SELECT ReferenceId
FROM OPENJSON(@jsonInfo, '$.References')
WITH (
    ReferenceId UNIQUEIDENTIFIER '$.ReferenceId'
)

提取所有Options值 #

如果需要关联对应所属的资源Id，可使用以下写法：

SELECT 
    res.Id AS ResourceId,
    opt.value AS OptionValue
FROM OPENJSON(@jsonInfo, '$.Resources')
WITH (
    Id UNIQUEIDENTIFIER '$.Id',
    Options NVARCHAR(MAX) '$.Options' AS JSON
) res
CROSS APPLY OPENJSON(res.Options) opt

补充说明 #

如果你只需要提取特定位置的单个值，可以直接用JSON_VALUE指定数组索引，示例如下：

-- 取References数组第一个元素的ReferenceId
SELECT JSON_VALUE(@jsonInfo, '$.References[0].ReferenceId')
-- 取Resources数组第一个元素的Options数组第一个值
SELECT JSON_VALUE(@jsonInfo, '$.Resources[0].Options[0]')

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