jsonapache-nifijolt

Convert Local time to GMT in a JSON using JOLT


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

Solution

  • You can perform;

    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 :

    enter image description here

    along with an added property named ts which has the value $[0].ts