Имеется часть JSON файла, который хранится в Azure Blob Storage и из которого необходимо экспортировать данные в Azure SQL DB при помощи TSQL скрипта:
"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
Основная проблема в повторяющихся в массивах параметрах from, to, seconds и тд. Ума не приложу, как извернуться, чтобы распарсить эту часть в табличную форму. Посоветуйте, пожалуйста. Желательно что-нибудь кроме "иди и учи мат. часть" :)
Мой скрипт:
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;
Ошибка - 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'.
Спасибо!
Простой
