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;
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;