Convert Local time to GMT in a JSON using JOLT

Question

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"
}        

• All measures saved as Key Value pairs
• stationname = station_Name + station_id
• Date" = local_time_last_update converted to GMT (ts field also can be converted to GMT)

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"
      }
    }
  }
]

Answer 1

You can perform;

• the concatenation for "stationname" attribute within the modify transformation as well
• date conversion from unix epoch to standard timestamp through use of 'GMT' literal as the last argument for the current one

such 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