时间戳

首页 > 解决方案 > 用于分层 JSON 的 T-SQL

json - 用于分层 JSON 的 T-SQL

问题描述

我需要从位于 Azure Blob 存储中的 JSON 中提取数组到 Azure SQL DB：

"vehicleStatusResponse": {
    "vehicleStatuses": [
        {
            "vin": "ABC1234567890",
            "triggerType": {
                "triggerType": "TIMER",
                "context": "RFMS",
                "driverId": {
                    "tachoDriverIdentification": {
                        "driverIdentification": "123456789",
                        "cardIssuingMemberState": "BRA",
                        "driverAuthenticationEquipment": "CARD",
                        "cardReplacementIndex": "0",
                        "cardRenewalIndex": "1"
                    }
                }
            },
            "receivedDateTime": "2020-02-12T04:11:19.221Z",
            "hrTotalVehicleDistance": 103306960,
            "totalEngineHours": 3966.6216666666664,
            "driver1Id": {
                "tachoDriverIdentification": {
                    "driverIdentification": "BRA1234567"
                }
            },
            "engineTotalFuelUsed": 48477520,
            "accumulatedData": {
                "durationWheelbaseSpeedOverZero": 8309713,
                "distanceCruiseControlActive": 8612200,
                "durationCruiseControlActive": 366083,
                "fuelConsumptionDuringCruiseActive": 3064170,
                "durationWheelbaseSpeedZero": 5425783,
                "fuelWheelbaseSpeedZero": 3332540,
                "fuelWheelbaseSpeedOverZero": 44709670,
                "ptoActiveClass": [
                    {
                        "label": "wheelbased speed >0",
                        "seconds": 16610,
                        "meters": 29050,
                        "milliLitres": 26310
                    },
                    {
                        "label": "wheelbased speed =0",
                        "seconds": 457344,
                        "milliLitres": 363350

我的带有 OPENJSON 的 T-SQL 脚本：


DECLARE @json AS NVARCHAR(MAX);

SELECT @json = r.BulkColumn
    FROM OPENROWSET (BULK 'response.json', DATA_SOURCE = '34524', SINGLE_CLOB) AS r

SELECT * FROM OPENJSON (@json, '$.vehicleStatusResponse.vehicleStatuses' )
WITH (
    vin NVARCHAR(50)  '$.vin',
    triggerType NVARCHAR(50)  '$.triggerType.triggerType',
    driverIdentification NVARCHAR(50)     '$.triggerType.driverId.tachoDriverIdentification.driverIdentification',
    cardIssuingMemberState NVARCHAR(50)   '$.triggerType.driverId.tachoDriverIdentification.cardIssuingMemberState',
    receivedDateTime DATETIME    '$.receivedDateTime',
    hrTotalVehicleDistance INT '$.hrTotalVehicleDistance',
    totalEngineHours FLOAT '$.totalEngineHours',
    engineTotalFuelUsed INT  '$.engineTotalFuelUsed',
    durationWheelbaseSpeedOverZero INT '$.accumulatedData.durationWheelbaseSpeedOverZero',
    distanceCruiseControlActive INT '$.accumulatedData.distanceCruiseControlActive',
    durationCruiseControlActive INT '$.accumulatedData.durationCruiseControlActive',
    fuelWheelbaseSpeedZero INT '$.accumulatedData.fuelWheelbaseSpeedZero',
    fuelWheelbaseSpeedOverZero INT '$.accumulatedData.fuelWheelbaseSpeedOverZero',
      ptoActiveClass NVARCHAR(MAX) '$.accumulatedData.ptoActiveClass' AS JSON

    ) as FIRST

CROSS APPLY OPENJSON (ptoActiveClass) 
WITH (
    label NVARCHAR(50),
    seconds INT,
    meters INT
);

我遇到了这个问题，当 PTOActiveClass 数组“标签”使表中的其他行加倍时。我需要为每个标签获取不同的列（轴距速度 > 0，轴距 = 0）。

我应该纠正什么？我需要为我的文凭工作解决进一步的数据分析问题：

PTOActiveClass 标签

标签： jsonsql-serverazuretsqlazure-sql-database

解决方案

一种选择是使用条件聚合，例如

SELECT vin, triggerType, driverIdentification, cardIssuingMemberState, receivedDateTime, 
       hrTotalVehicleDistance, totalEngineHours, engineTotalFuelUsed, durationWheelbaseSpeedOverZero, 
       distanceCruiseControlActive, durationCruiseControlActive, fuelWheelbaseSpeedZero, fuelWheelbaseSpeedOverZero, 
       MAX(CASE WHEN l.label = 'wheelbased speed =0' THEN seconds END) AS [seconds for speed =0],
       MAX(CASE WHEN l.label = 'wheelbased speed >0' THEN seconds END) AS [seconds for speed >0],
       MAX(CASE WHEN l.label = 'wheelbased speed =0' THEN meters  END) AS [meters for speed =0],
       MAX(CASE WHEN l.label = 'wheelbased speed >0' THEN meters  END) AS [meters for speed >0]
  FROM OPENJSON (@json, '$.vehicleStatusResponse.vehicleStatuses' )
  WITH (
        vin                            NVARCHAR(50)  '$.vin',
        triggerType                    NVARCHAR(50)  '$.triggerType.triggerType',
        driverIdentification           NVARCHAR(50)  '$.triggerType.driverId.tachoDriverIdentification.driverIdentification',
        cardIssuingMemberState         NVARCHAR(50)  '$.triggerType.driverId.tachoDriverIdentification.cardIssuingMemberState',
        receivedDateTime               DATETIME      '$.receivedDateTime',
        hrTotalVehicleDistance         INT           '$.hrTotalVehicleDistance',
        totalEngineHours               FLOAT         '$.totalEngineHours',
        engineTotalFuelUsed            INT           '$.engineTotalFuelUsed',
        durationWheelbaseSpeedOverZero INT           '$.accumulatedData.durationWheelbaseSpeedOverZero',
        distanceCruiseControlActive    INT           '$.accumulatedData.distanceCruiseControlActive',
        durationCruiseControlActive    INT           '$.accumulatedData.durationCruiseControlActive',
        fuelWheelbaseSpeedZero         INT           '$.accumulatedData.fuelWheelbaseSpeedZero',
        fuelWheelbaseSpeedOverZero     INT           '$.accumulatedData.fuelWheelbaseSpeedOverZero',
        ptoActiveClass                 NVARCHAR(MAX) '$.accumulatedData.ptoActiveClass' AS JSON
    ) 
 CROSS APPLY OPENJSON (ptoActiveClass) 
 WITH (
       label   NVARCHAR(50),
       seconds INT,
       meters  INT
 ) AS l
GROUP BY vin, triggerType, driverIdentification, cardIssuingMemberState, receivedDateTime, 
         hrTotalVehicleDistance, totalEngineHours, engineTotalFuelUsed, durationWheelbaseSpeedOverZero, 
         distanceCruiseControlActive, durationCruiseControlActive, fuelWheelbaseSpeedZero, fuelWheelbaseSpeedOverZero;

其中@json变量的值应以左花括号为前缀，{并以花括号和方括号为后缀 : } ] } } ] } }。

ISJSON()函数可用于检查SELECT ISJSON( @json )查询是否返回 1（有效）或 0（无效）的有效性。

Demo

推荐阅读