如何用CROSS APPLY OPENJSON查询JSON中动态分组的metrics数据

使用CROSS APPLY OPENJSON查询CosmosDB中的JSON文档时，当前查询只能指定$.general这类具体键名来关联metrics数据，无法自动获取metrics下所有分组（比如general、costInfo等）的metrics数据，希望实现无需指定具体键名即可获取所有分组的metrics数据。

给定的JSON文档结构：

{
    "label": "Test Run",
    "id": "980b6df5-2d36-433f-8379-5ae80698ef7a",
    "activities": [
        {
            "id": "8fe33644-5a23-4522-8a67-7e6e898d8252",
            "activityId": "c87807ec-2114-4ca5-ad0a-b0aed5c83b40",
            "name": "Test",
            "metrics": {
                "general": [
                    {
                        "order": 1,
                        "name": "start_time",
                        "label": "start_time",
                        "dataType": "String",
                        "value": "2024-04-18T19:49:35.385929UTC"
                    },
                    {
                        "order": 2,
                        "name": "end_time",
                        "label": "end_time",
                        "dataType": "String",
                        "value": "2024-04-18T19:50:06.800318UTC"
                    },
                    {
                        "order": 3,
                        "name": "duration",
                        "label": "duration",
                        "dataType": "String",
                        "value": "0:31.4"
                    }
                ],
              "costInfo":[
                {
                    "order": 1,
                    "name": "amount",
                    "label": "amount",
                    "dataType": "String",
                    "value": "4.54"
                }
              ]
            },
            "startedOnUtc": "2024-04-18T19:49:35.3683996Z",
            "completedOnUtc": "2024-04-18T19:50:07.168983Z"
        }
    ],
    "createdOnUtc": "2024-04-18T19:48:05.7894172Z"
}

现有查询（仅能指定具体键名）：

SELECT TOP 100 
    FlowRun.id, 
    FlowRun.label, 
    a.id as activityId, 
    a.name as activityName, 
    metric.*
FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=xxxx-yyyyyy;Database=Application',
                OBJECT = 'FlowRun',
                SERVER_CREDENTIAL = 'xxxx-yyyyyy'
) AS FlowRun
    CROSS APPLY OPENJSON ( FlowRun.activities )
                  WITH (
                       id UNIQUEIDENTIFIER,
                       name varchar(50),
                       metrics nvarchar(max) as json
                  ) AS a
    CROSS APPLY OPENJSON(a.metrics, '$.general') 
    WITH (
        [order] INT '$.order',
        [name] NVARCHAR(50) '$.name',
        [label] NVARCHAR(50) '$.label',
        [dataType] NVARCHAR(50) '$.dataType',
        [value] NVARCHAR(50) '$.value'
    ) AS metric;

要遍历metrics下的所有分组，需要先解析metrics的顶层键（比如general、costInfo），再对每个键对应的数组进行解析。具体步骤如下：

1. 先通过OPENJSON(a.metrics)获取metrics的所有顶层键和对应的值（数组），无需指定路径，默认遍历所有键值对。
2. 再对每个键对应的数组值，再次使用OPENJSON解析成具体的metric字段，同时可以保留分组名称方便区分来源。

修改后的查询：

SELECT TOP 100 
    FlowRun.id, 
    FlowRun.label, 
    a.id as activityId, 
    a.name as activityName,
    metric_group.[key] as metric_group_name, -- 新增分组名称字段，标识该metric属于哪个分组
    metric.*
FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=xxxx-yyyyyy;Database=Application',
                OBJECT = 'FlowRun',
                SERVER_CREDENTIAL = 'xxxx-yyyyyy'
) AS FlowRun
    CROSS APPLY OPENJSON ( FlowRun.activities )
                  WITH (
                       id UNIQUEIDENTIFIER,
                       name varchar(50),
                       metrics nvarchar(max) as json
                  ) AS a
    -- 第一步：解析metrics的所有顶层键值对，key是分组名，value是对应的metric数组
    CROSS APPLY OPENJSON(a.metrics) AS metric_group
    -- 第二步：解析每个分组下的metric数组
    CROSS APPLY OPENJSON(metric_group.[value]) 
    WITH (
        [order] INT '$.order',
        [name] NVARCHAR(50) '$.name',
        [label] NVARCHAR(50) '$.label',
        [dataType] NVARCHAR(50) '$.dataType',
        [value] NVARCHAR(50) '$.value'
    ) AS metric;

说明 #

• 新增的metric_group.[key]字段会返回该metric所属的分组名称（比如general、costInfo），方便后续区分不同分组的metrics数据。
• 两层CROSS APPLY嵌套实现了先遍历metrics的所有分组，再遍历每个分组下的所有metric项，无需指定具体的分组键名，自动适配任意数量的分组。

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