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"
}
}
}
]
Expected JSON Output :
{
"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"
}
]
}
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!