代码之家 › 专栏 › 技术社区 › Mike

在字典<string,object[]>上交叉应用OPENJSON

open-json cross-apply azure-synapse t-sql

1

Mike · 技术社区 · 11 月前

我试图使用交叉应用openjson查询json文档,我非常接近预期的结果,但我不知道如何加入一个有N个键名的字典。

考虑以下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"
}

下面的查询使用标识符“$.general”连接度量。如何避免指定“$.general”,以便我也可以获取“$.costInfo”?可能有许多不同的组,我想在这些组下获得所有指标。

我尝试过“$.*”和其他一些尝试,但都没有成功。

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;

1 回复 | 直到 11 月前

1

0

Vaibhav Gupta 11 月前

为了解决查询的问题,您需要动态访问多个嵌套数组,而无需显式指定每个数组。您可以通过使用 OPENJSON 迭代地遍历每个级别。以下是一种修改查询的方法:

SELECT 
    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) AS jsonData
    CROSS APPLY OPENJSON(jsonData.value) WITH (
        [order] INT '$.order',
        [name] NVARCHAR(50) '$.name',
        [label] NVARCHAR(50) '$.label',
        [dataType] NVARCHAR(50) '$.dataType',
        [value] NVARCHAR(50) '$.value'
    ) AS metric;

此解决方案允许您访问不同的度量组( general , costInfo 等等)而不明确地命名它们。每个 CROSS APPLY OPENJSON step处理JSON层次结构中的一个级别。