SQL Server如何按指定RoleName读取JSON列中嵌套数组的字段值

背景说明 #

SQL Server 内置的JSON路径语法暂不支持[?(@.属性=='值')]这类条件过滤表达式，所以你原有顶层数组结构下，无法直接通过JSON_VALUE指定RoleName一步查询到结果，必须借助OPENJSON做解析。下面提供两种可选方案： #

方案1：不调整现有JSON结构，简化OPENJSON写法 #

对比你原有写法可以去掉多余的嵌套子查询，直接针对业务表查询：

场景1：查询指定RoleName下所有ContactName拼接结果 #

SELECT STRING_AGG(jc.ContactName, ',') AS ContactName
FROM tblMyTable
CROSS APPLY OPENJSON(JSONColumn) WITH (
    RoleName VARCHAR(50),
    Roles NVARCHAR(MAX) AS JSON
) jr
CROSS APPLY OPENJSON(jr.Roles) WITH (
    ContactName VARCHAR(255)
) jc
WHERE Column1 = 1 AND jr.RoleName = 'Client'

场景2：查询指定RoleName下第一个Roles的ContactName #

SELECT TOP 1 jc.ContactName
FROM tblMyTable
CROSS APPLY OPENJSON(JSONColumn) WITH (
    RoleName VARCHAR(50),
    Roles NVARCHAR(MAX) AS JSON
) jr
CROSS APPLY OPENJSON(jr.Roles) WITH (
    ContactName VARCHAR(255)
) jc
WHERE Column1 = 1 AND jr.RoleName = 'Owner'

方案2：调整JSON结构，支持直接路径查询 #

如果可以修改存储的JSON结构，建议把顶层数组改为以RoleName为key的对象，结构示例：

{
    "Client": {
        "Roles": [
            {
                "ContactID": 21568,
                "ContactName": "FullName1"
            },
            {
                "ContactID": 31568,
                "ContactName": "FullName2"
            }
        ]
    },
    "Owner": {
        "Roles": [
            {
                "ContactID": 1,
                "ContactName": "Billy Buxton"
            }
        ]
    }
}

调整后可以大幅简化查询逻辑：

场景1：查询指定RoleName下第一个Roles的ContactName #

直接用JSON_VALUE即可实现：

SELECT JSON_VALUE(JSONColumn, '$.Owner.Roles[0].ContactName') AS ContactName
FROM tblMyTable
WHERE Column1 = 1

场景2：查询指定RoleName下所有ContactName拼接结果 #

仅需要一层OPENJSON即可：

SELECT STRING_AGG(ContactName, ',') AS ContactName
FROM tblMyTable
CROSS APPLY OPENJSON(JSON_QUERY(JSONColumn, '$.Client.Roles')) WITH (
    ContactName VARCHAR(255)
)
WHERE Column1 = 1

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