Json - Change the date format in JOLT

Casper
September 18, 2024
90 views
0 votes
2 Answers

In the attached JOLT, I want to change the date format, type conversion and string operation as follows.

Date needs to change into yyyy-mm-dd hh:mm:ss format (2024-06-16 23:54:14) from UTC (2024-06-16T23:54:14.711456Z)
The value of WEIGHT code needs to convert into decimal from string
for machinename, needs to get only the name

Can someone please help on this. Thank you

Input JSON

{
  "machine": "26619",
  "mname": "MapRecord[{name=machine_col}]",
  "timestamp": "2024-06-16T23:54:14.711456Z",
  "temp": "16.0",
  "height": "0.151",
  "site": "2343",
  "weight": "0.2955",
  "length": "0.2"
}

JOLT:

[
  {
    "operation": "shift",
    "spec": {
      "mname": "machinename",
      "timestamp": "Date",
      "temp": {
        "@": "data[0].value",
        "#A2-P1-M1 5cm": "data[0].code"
      },
      "height": {
        "@": "data[1].value",
        "#A1-P1-T1 5cm": "data[1].code"
      },
      "weight": {
        "@": "data[2].value",
        "#WEIGHT": "data[2].code"
      }
    }
  }
 ]

Output JSON:

{
  "machinename" : "machine_col",
  "Date" : "2024-06-16 23:54:14",
  "data" : [ {
    "value" : "16.0",
    "code" : "A2-P1-M1 5cm"
  }, {
    "value" : "0.151",
    "code" : "A1-P1-T1 5cm"
  }, {
    "value" : 0.2955,
    "code" : "WEIGHT"
  } ]
}

Tags: jolt json

Answers

You can use the following transformation spec :

[
  { //derive "machinename" and "Date" attributes
    "operation": "modify-overwrite-beta",
    "spec": {
      "mname": "=split('name=',@(1,&))",
      "mname_": "=split('}',@(1,mname))",
      "machinename": "=(@(1,mname_[1][0]))",
      "timestamp": "=split('T',@(1,&))",
      "timestamp_": "=join(' ',@(1,timestamp))",
      "timestamp__": "=split('Z',@(1,timestamp_))",
      "Date": "=(@(1,timestamp__[0]))",
      "weight": "=toDouble" //convert to decimal format
    }
  },
  { //form two independent arrays "value" vs. "code"
    "operation": "shift",
    "spec": {
      "machinename|Date": "&",
      "temp": {
        "@": "data.value",
        "#A2-P1-M1 5cm": "data.code"
      },
      "height": {
        "@": "data.value",
        "#A1-P1-T1 5cm": "data.code"
      },
      "weight": {
        "@": "data.value",
        "#WEIGHT": "data.code"
      }
    }
  },
  {//tile the attributes regarding their orders of the elements within the arrays
    "operation": "shift",
    "spec": {
      "*": "&",
      "data": {
        "*": {
          "*": {
            "@": "&3[&1].&2"
          }
        }
      }
    }
  }
]

Hi I have modified your spec by adding few operations before it.

 [
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "weight": "=toDouble",
      "mnameSplit1": "=split('=', @(1,mname))",
      "mnameVar1": "@(1,mnameSplit1[1])",
      "mnameSplit2": "=split('}]', @(1,mnameVar1))",
      "mname": "@(1,mnameSplit2[0])",
      "timestamp": "=split('T',@(1,&))"
    }
  }, {
    "operation": "modify-overwrite-beta",
    "spec": {
      "timestampVar": "=substring(@(1,timestamp[1]),0,8)"
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "timestamp": "=join(' ',@(1,timestamp[0]),@(1,timestampVar))"
    }
  },
  {
    "operation": "remove",
    "spec": {
      "mnameSplit1": "",
      "mnameVar1": "",
      "mnameSplit2": "",
      "mnameVar2": "",
      "timestampVar": ""
    }
  },
  {
    "operation": "shift",
    "spec": {
      "mname": "machinename",
      "timestamp": "Date",
      "temp": {
        "@": "data[0].value",
        "#A2-P1-M1 5cm": "data[0].code"
      },
      "height": {
        "@": "data[1].value",
        "#A1-P1-T1 5cm": "data[1].code"
      },
      "weight": {
        "@": "data[2].value",
        "#WEIGHT": "data[2].code"
      }
    }
  }
 ]

And Jolt currently don’t provide utility to change Date to required format but I have done it through modify-overwrite-beta by splitting with T and joining with empty space and also trimming the values till the length required in your date format.

Hope this helps!

Please signup or login to give your own answer.

Click here to cancel reply.

Json – Change the date format in JOLT

Answers