H
H
hatorihanso2020-05-25 18:19:23
SQL
hatorihanso, 2020-05-25 18:19:23

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

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 the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2020-05-25
@hatorihanso

JSON data in SQL Server .

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

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question