json - 用于分层 JSON 的 T-SQL

Question

我需要将位于 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)。

我应该纠正什么？我需要解决这个问题以进行我的文凭工作的进一步数据分析:

Answer 1

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

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