I have a JSON input which I convert into another JSON using JOLT notation. I have measures which needs to save as key value pairs, station name & station no combined for one field and date field needs to be saved separately. I have produced my output accordingly except getting the time and converting it to GMT. Please have look on my input JSON and Expected output JSON and modify the my current JOLT.
Input JSON :
[
{
"station_id": 1919,
"local_time_last_update": "2024-11-05 10:45:21",
"ts": 1730796321,
"temperature": 19.7,
"humidity": 49,
"pressure": 1026.87,
"wind_average": 1.6,
"wind_direction": 270,
"total_rain": 0,
"solar_radiation": 0,
"FDI": 38,
"fault_status": 0,
"station_Name": "Silicon Valley "
}
]
Expected Output :
{
"data": [
{
"code": "temperature",
"value": 19.7
},
{
"code": "humidity",
"value": 49
},
{
"code": "pressure",
"value": 1026.87
},
{
"code": "wind_average",
"value": 1.6
},
{
"code": "wind_direction",
"value": 270
},
{
"code": "total_rain",
"value": 0
},
{
"code": "solar_radiation",
"value": 0
},
{
"code": "FDI",
"value": 38
},
{
"code": "fault_status",
"value": 0
}
],
"stationname": "1919_Silicon Valley",
"Date": "2024-11-05 08:45:21"
}
Current JOLT Spec :
[
{
"operation": "modify-overwrite-beta",
"spec": {
"*": {
"timestamp": "${ts:multiply(1000):format('yyyy-MM-dd HH:mm:ss', 'Etc/GMT-1')}"
}
}
},
{
"operation": "remove",
"spec": {
"*": {
"local_time_last_update": "",
"ts": "",
"station_id": ""
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"timestamp": "Date",
"*": {
"$": "data[#2].code",
"@": "data[#2].value"
},
"station_Name": "stationname"
}
}
}
]
You can perform;
"stationname"
attribute within the modify
transformation as well'GMT'
literal as the last argument for the current onesuch as
[
{
"operation": "modify-overwrite-beta",
"spec": {
"*": {
"stationname": "=concat(@(1,station_id),'_',@(1,station_Name))",
"Date": "${ts:multiply(1000):format('yyyy-MM-dd HH:mm:ss', 'GMT')}"
}
}
},
{
"operation": "remove",
"spec": {
"*": {
"local_time_last_update": "",
"ts": "",
"station_id": ""
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"*": {
"$": "data[#2].code",
"@": "data[#2].value"
},
"stationname|Date": "&"
}
}
}
]
where it's important to express the fact that the variable cannot be directly used within a JoltTransformJSON processor, but should be pre-processed, preferably might be taken from an
EvaluateJsonPath processor :
along with an added property named ts
which has the value $[0].ts