如何将JSON数组解析为SQL SERVER 2016

Russ 发表于 Dev

拉斯

我正在寻找从下面的JSON创建数据表（请参见“输出表”）。我似乎无法达到“最后一英里”。有人可以告诉我正确处理将数组解析为所需输出表的方法吗？

谢谢！拉斯

所需的输出表

我得到的输出

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

查理菲斯

很难知道timestamps和之间的相关性values，但是我已经假设它是通过数组索引来进行的。

是否dimensions总是在数组中的单个值？我以为是这样。

然后，您需要使用OPENJSON（且没有WITH块）来分解两个数组，并根据index将它们连接在一起，该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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。