What is the correct way to use TSQL for JSON parsing?

Question

SQL

There is a part of a JSON file that is stored in Azure Blob Storage and from which you need to export data to Azure SQL DB using a TSQL script:

"accelerationPedalPositionClass": [
                        {
                            "from": 0.0,
                            "to": 20.0,
                            "seconds": 9516823
                        },
                        {
                            "from": 20.0,
                            "to": 40.0,
                            "seconds": 2508318
                        },
                        {
                            "from": 40.0,
                            "to": 60.0,
                            "seconds": 1154166
                        },
                        {
                            "from": 60.0,
                            "to": 80.0,
                            "seconds": 524101
                        },
                        {
                            "from": 80.0,
                            "to": 100.0,
                            "seconds": 572205
                        }
                    ],
                    "accelerationClass": [
                        {
                            "to": -1.1,
                            "seconds": 176031,
                            "meters": 1716900,
                            "milliLitres": 468620
                        },
                        {
                            "from": -1.1,
                            "to": -0.9,
                            "seconds": 45066,
                            "meters": 458630,
                            "milliLitres": 143940

The main problem is in the parameters from, to, seconds, etc., repeated in arrays. I'll never know how to dodge to parse this part into a tabular form. Please advise. Preferably something other than "go and learn the mat. part" :)

My script:

DECLARE @json AS NVARCHAR(MAX);

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

SELECT vin, triggerType, driverIdentification, cardIssuingMemberState, receivedDateTime, 
       hrTotalVehicleDistance, totalEngineHours, engineTotalFuelUsed, durationWheelbaseSpeedOverZero, 
       distanceCruiseControlActive, durationCruiseControlActive, fuelWheelbaseSpeedZero, fuelWheelbaseSpeedOverZero,
       brakePedalCounterSpeedOverZero,  distanceBrakePedalActiveSpeedOverZero, drivingWithoutTorqueClass, 
       brakeCount, engineTotalCatalystUsed, stopCount
       MAX(CASE WHEN l.label = 'wheelbased speed =0' THEN seconds END) AS [seconds for wheelbased speed =0],
       MAX(CASE WHEN l.label = 'wheelbased speed >0' THEN seconds END) AS [seconds for wheelbased speed >0],
       MAX(CASE WHEN l.label = 'wheelbased speed =0' THEN milliLitres  END) AS [ milliLitres for wheelbased speed =0],
       MAX(CASE WHEN l.label = 'wheelbased speed >0' THEN meters  END) AS [meters for wheelbased speed >0],
       MAX(CASE WHEN l.label = 'wheelbased speed >0' THEN milliLitres  END) AS [milliLitres for wheelbased speed >0],
       MAX(CASE WHEN t.label = 'AUTO' THEN value END) AS [AUTO for transmissionModeSeconds],
       MAX(CASE WHEN t.label = 'MANUAL' THEN value END) AS [MANUAL for transmissionModeSeconds],
       MAX(CASE WHEN t.label = 'POWER' THEN value END) AS [POWER for transmissionModeSeconds],
       MAX(CASE WHEN c.label = 'LIGHT' THEN value END) AS [LIGHT convoyWeightMeters],
       MAX(CASE WHEN c.label = 'MEDIUM' THEN value END) AS [MEDIUM convoyWeightMeters],
       MAX(CASE WHEN c.label = 'MEDIUM' THEN value END) AS [FULLLOAD convoyWeightMeters]

  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,
        brakePedalCounterSpeedOverZero INT           '$.accumulatedData.brakePedalCounterSpeedOverZero',
        distanceBrakePedalActiveSpeedOverZero INT      '$.accumulatedData.distanceBrakePedalActiveSpeedOverZero',
        drivingWithoutTorqueClass INT '$.accumulatedData.drivingWithoutTorqueClass',
        brakeCount INT '$.accumulatedData.DataAccumulated.brakeCount',
        transmissionModeSeconds NVARCHAR(MAX) '$.accumulatedData.DataAccumulated.transmissionModeSeconds' AS JSON,
        convoyWeightMeters NVARCHAR(MAX) '$.accumulatedData.DataAccumulated.convoyWeightMeters' AS JSON,
        stopCount INT '$.accumulatedData.DataAccumulated.stopCount',
        engineTotalCatalystUsed INT '$.accumulatedData.DataAccumulated.engineTotalCatalystUsed'



        

    )
 CROSS APPLY OPENJSON (ptoActiveClass) 
 WITH (
       label   NVARCHAR(50),
       seconds INT,
       meters  INT,
       milliLitres INT
 ) AS l

 CROSS APPLY OPENJSON (transmissionModeSeconds)
 WITH (
        label NVARCHAR(50),
        value INT
 ) AS t

 CROSS APPLY OPENJSON (convoyWeightMeters)
 WITH (
        label NVARCHAR (50),
        value INT
 ) AS c

GROUP BY receivedDateTime, vin, triggerType, driverIdentification, cardIssuingMemberState, 
         hrTotalVehicleDistance, totalEngineHours, engineTotalFuelUsed, durationWheelbaseSpeedOverZero, 
         distanceCruiseControlActive, durationCruiseControlActive, fuelWheelbaseSpeedZero, fuelWheelbaseSpeedOverZero,
         brakePedalCounterSpeedOverZero,  distanceBrakePedalActiveSpeedOverZero, drivingWithoutTorqueClass, brakeCount, stopCount, engineTotalCatalystUsed;

Error - Failed to execute query. Error: Ambiguous column name 'value'.
Ambiguous column name 'value'.
Ambiguous column name 'value'.
Ambiguous column name 'value'.
Ambiguous column name 'value'.
Ambiguous column name 'value'.

Thank you!

Answer 1

JSON data in SQL Server .

Ambiguous column name 'value'.

…
CASE WHEN t.label = 'AUTO' THEN t.value END
…