T-SQL for hierarchical JSON

Question

I need to extract arrays from JSON, which is located in Azure Blob Storage, to 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

My T-SQL script with OPENJSON:


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

And I faced with the issue, when PTOActiveClass array "label" makes double other rows in the table. I need to get different columns for each label (wheelbase speed > 0, wheelbase = 0).

What should I correct? I need to solve it to the further data analysis for my diploma work:

user_5841306 · Answer

One option would be using conditional aggregation such as

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;

where value of @json variable should be prefixed by an opening curly brace { and suffixed by those curly braces and square brackets : } ] } } ] } }.

ISJSON() function might be used to check validity whether SELECT ISJSON( @json ) query returns 1(valid) or 0(invalid).

Demo

T-SQL for hierarchical JSON

1 Answers