t-sqlazure-synapsecross-applyopen-json

CROSS APPLY OPENJSON on Dictionary<string, object[]>


I'm trying to query against json documents using cross apply openjson and i'm very close to the desired result but I don't see how to join on a dictionary with N number of key names.

Consider the following json document(s) schema:

{
    "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"
}

And the following query which joins down the the metrics using the identifier '$.general'. How can I avoid specifying '$.general' so that I can also get the '$.costInfo'? There could be a number of different groups under which I want to get all the metrics for.

I've tried '$.*' and a few others attempts with no luck.

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;

Solution

  • How can I avoid specifying '$.general' so that I can also get the '$.costInfo'?

    Try below query to get information from both arrays without specifying $.

    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')
      with (
            id UNIQUEIDENTIFIER,
            label NVARCHAR(50),
            activities NVARCHAR(MAX) AS JSON
      ) 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 metricGroups
        CROSS APPLY OPENJSON(metricGroups.value) 
        WITH (
            [order] INT '$.order',
            [name] NVARCHAR(50) '$.name',
            [label] NVARCHAR(50) '$.label',
            [dataType] NVARCHAR(50) '$.dataType',
            [value] NVARCHAR(50) '$.value'
        ) AS metric;