How to transform a JSON to another JSON format using JOLT Notation

Input JSON :

{
  "sid": "1",
  "poste": "MapRecord[{altitude=473.0, latitude=49.92665, poste=Libramont, longitude=5.36080}]",
  "mtime": "2023-07-12T01:00:00Z",
  "tsa": "17.2",
  "tha": "16.2",
  "hra": "90.2",
  "tsf": "15.8",
  "tss": "21.4",
  "ens": "0.0",
  "dvt": "319.6",
  "vvt": "0.9",
  "plu": "0.0",
  "hct": "60.0"
}

Desired Output JSON :

{
  "Date": "2023-03-14 01:00:00",
  "stationname": "Libramont",
  "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.0"
    },
    {
      "code": "ens",
      "value": "0.0"
    },
    {
      "code": "dvt",
      "value": "0"
    },
    {
      "code": "vvt",
      "value": "4.8"
    },
    {
      "code": "plu",
      "value": "0.1"
    },
    {
      "code": "hct",
      "value": "26.0"
    }
  ]
}

As shown in the output I need to turn only my measures into key value pairs (remove the poste information). Also I need to map the relevant name from the poste attribute displayed as the "stationname" like in my output. Also I need to convert nulls into zeros and date properly formatted. Thanks is Advance!

Solution

You can use the following transformation

[
  {
    "operation": "shift",
    "spec": {
      "mtime": "Date", // rename the attribute
      "poste": "&",
      "sid": "&", // the "sid" attribute will vanish later by separating here
      "*": "data.&"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "Date|poste": "&",
      "data": {
        "*": { // loop through all the attributes within the "data" array
          "$": "&2[#2].code",
          "@": "&2[#2].value"
        }
      }
    }
  },
  { // extract the value of "poste"
    "operation": "modify-overwrite-beta",
    "spec": {
      "data": {
        "*": {
          "value": ["=notNull", 0] // If "Not Null" is NOT the case(eg. "null"), then set to zero
        }
      },
      "DPortion1": "=substring(@(1,Date),0,10)",
      "DPortion2": "=substring(@(1,Date),11,19)",
      "Date": "=concat(@(1,DPortion1),' ',@(1,DPortion2))",
      "poste": "=split('poste=',@(1,&))",
      "po1": "=lastElement(@(1,poste))",
      "po2": "=split(',',@(1,po1))",
      "stationname": "=firstElement(@(1,po2))"
    }
  },
  { // return the desired elements sorted
    "operation": "shift",
    "spec": {
      "Date|stationname|data": "&"
    }
  }
]

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