I am a newbie to Nifi so lack still in learning this too. I have a task to get data from executeSQL and the record return like this:
[{
"PKG_HA_01_ON":2.0,
"PKG_HA_03_ON":28.0,
"PKG_HA_04_ON":7.0,
"PKG_HA_05_ON":0.0,
"PKG_HA_06_ON":1.0,
"PKG_HA_09_ON":5.0
},
{
"PKG_HA_01_ON":8.02905,
"PKG_HA_03_ON":57.29038333333333,
"PKG_HA_04_ON":2.2858666666666663,
"PKG_HA_05_ON":60.0,
"PKG_HA_06_ON":12.291533333333332,
"PKG_HA_09_ON":12.3363
}]
This is the result of a union query. Now, I would like to convert into this:
[{
"machine":"PKG_HA_01_ON",
"counter":2.0,
"duration":8.02905
},
{
"machine":"PKG_HA_03_ON",
"counter":28.0,
"duration":57.29038333333333
}]
I have researched on JoltTransformJSON but still stuck with it. So what is the best way to achieve desired JSON?
Thanks in advance!!!
You can consecutively apply shift transformation as
[
{
"operation": "shift",
"spec": {
"*": {
"@PKG_HA_01_ON": "PKG_HA_01_ON.&",
"@PKG_HA_03_ON": "PKG_HA_03_ON.&"
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"$": "&.machine",
"@(1,&.0)": "&.counter",
"@(1,&.1)": "&.duration"
}
}
},
{
"operation": "shift",
"spec": {
"*": ""
}
}
]
Restrict the result set to two keys(PKG_HA_01/3_ON) in the first step while determining the values for the keys 0 and 1 respectively, then convert those numbered keys to yours (counter and duration respectively) while adding the machine element, and remove the key names at the last step.