How to Parse JSON Arrays to SQL SERVER 2016

user_15269166 0
Viewed 47

I'm looking to create a table of data (see "Output Table") from the below JSON. I can't seem to get to the "final mile". Can someone show me the way to properly handle the parsing of the arrays into the desired output table?

Thanks! Russ

Desired Output Table enter image description here

Output I am getting enter image description here

declare @json nvarchar(max) =
'{
    "totalCount": 1,
    "nextPageKey": null,
    "result": [
      {
        "metricId": "builtin:host.cpu.usage",
        "data": [
            {
              "dimensions": [
                "HOST-CCCC3F95D7CE56"
              ],
              "dimensionMap": {
                "dt.entity.host": "HOST-CCCC3F95D7CE56"
              },
              "timestamps": [
                1612634400000,
                1612645200000,
                1612656000000

               ],
               "values": [
                0.37900028935185187,
                0.3709309895833333,
                0.5088975694444444

              ]
            }
          ]
        }
    ]
}'

Select TableA.totalCount, TableResult.metricId,
TableDim.*
FROM OPENJSON(@json)
    WITH(
        totalCount  int,
        result      NVARCHAR(MAX) as JSON
    ) as TableA
CROSS APPLY OPENJSON(TableA.result)
    WITH(
        metricId    VARCHAR(100),
        data        NVARCHAR(MAX) as JSON
    )TableResult
CROSS APPLY OPENJSON(TableResult.data)
    WITH(
        dimensions  NVARCHAR(MAX) as JSON,
        timestamps  NVARCHAR(MAX) as JSON,
        [values]    NVARCHAR(MAX) as JSON
    )TableDim
sql-server json
1 Answers

Hard to know what the correlation is between timestamps and values, but I've assumed it's by array index.

Is dimensions always a single value in the array? I have assumed so.

You then need to use OPENJSON (and no WITH block) to break out the two arrays, and join them together on index, which is supplied in key:

Select TableA.totalCount, TableResult.metricId,
TableDim.dimensions, Vals.*
FROM OPENJSON(@json)
    WITH(
        totalCount  int,
        result      NVARCHAR(MAX) as JSON
    ) as TableA
CROSS APPLY OPENJSON(TableA.result)
    WITH(
        metricId    VARCHAR(100),
        data        NVARCHAR(MAX) as JSON
    )TableResult
CROSS APPLY OPENJSON(TableResult.data)
    WITH(
        dimensions  NVARCHAR(MAX) '$.dimensions[0]',
        timestamps  NVARCHAR(MAX) as JSON,
        [values]    NVARCHAR(MAX) as JSON
    )TableDim
CROSS APPLY (
    SELECT tm.value AS Timestamp, v.value AS Value
    FROM OPENJSON(TableDim.timestamps) tm
    JOIN OPENJSON(TableDim.[values]) v ON v.[key] = tm.[key]
) Vals
user_148689970
Related