How to transform a JSON to another JSON format using JOLT

Input JSON

{
  "version": "2.0",
  "terms_of_service": "2022 CCC",
  "frequency": "hourly",
  "references": {
    "stations": [
      {
        "sid": "46",
        "poste": "Colombo",
        "longitude": "80.29941",
        "latitude": "10.30466",
        "altitude": "100.0"
      }
    ]
  },
  "results": [
    {
      "mtime": "2023-03-14T00:00:00Z",
      "sid": "46",
      "tsa": "8.8",
      "tha": "7.7",
      "hra": "86.3",
      "tsf": null,
      "tss": null,
      "ens": "0.0",
      "dvt": null,
      "vvt": "4.6",
      "plu": "0.1",
      "hct": "60.0"
    }
  ]
}

Output JSON

{
  "Date": "2023-03-14 01:00:00",
  "data": [
    {
      "code": "tsa",
      "value": "9.0"
    },
    {
      "code": "tha",
      "value": "7.8"
    },
    {
      "code": "hra",
      "value": "85.0"
    },
    {
      "code": "tsf",
      "value": "0.0"
    },
    {
      "code": "tss",
      "value": "0"
    },
    {
      "code": "ens",
      "value": "0"
    },
    {
      "code": "dvt",
      "value": "0"
    },
    {
      "code": "vvt",
      "value": "4.8"
    },
    {
      "code": "plu",
      "value": "0.1"
    },
    {
      "code": "hct",
      "value": "26.0"
    }
  ],
  "stationname": "Colombo"
}

As shown in the output I need to turn my measures into key value pairs. Also I need to remove the sid from key value pairs but map the relevant name from the value of poste attribute nested within the stations array and display the "stationname" in my output. Also I need to convert nulls into zeros Thanks in Advance!

Solution

You can use the following transformation

[
  {
    "operation": "shift",
    "spec": {
      "results": {
        "*": {
          "mtime": "Date", // rename the attribute
          "sid": "&", // the "sid" attribute will vanish later by separating here
          "*": "data.&"
        }
      },
      "@references.stations[0].poste": "stationname"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": "&",
      "data": {
        "*": { // loop through all the attributes within the "data" array
          "$": "data[#2].code",
          "@": "data[#2].value"
        }
      }
    }
  },
  { // just to sort the elements in the deisred order
    "operation": "shift",
    "spec": {
      "Date|data": "&",
      "stationname": "&"
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "data": {
        "*": {
          "value": ["=notNull", 0] // else case, eg. If "Not Not Null", eg. If "Null", then zero
        }
      },
      "DPortion1": "=substring(@(1,Date),0,10)",
      "DPortion2": "=substring(@(1,Date),11,19)",
      "Date": "=concat(@(1,DPortion1),' ',@(1,DPortion2))" // overwrite the Date's value by portions 
    }
  },
  { // get rid of the DPortion parameters
    "operation": "remove",
    "spec": {
      "DPortion*": ""
    }
  }
]

the demo on the site http://jolt-demo.appspot.com/ is :