transform ExecuteSQL record into JSON in Apache Nifi

[#1]

Hi all, 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

[#2]

Hi,

I guess you can't edit the extract query and maybe use a join, correct?

Because the problem is that for nifi the 2 jsons in the array are potentially two representations of the same identity. So it is difficult to find a reliable method to achieve the goal.

I would re-start from data extraction ...

[#3]

Hi,

Thanks for your reply. I have edited my sql query to return as this format. So I just need to use EvaluateJsonPath to continue.

[
   {
      "machine":"PKG_HA_01_ON",
      "counter":2.0,
      "duration":8.02905
   },
   {
      "machine":"PKG_HA_03_ON",
      "counter":28.0,
      "duration":57.29038333333333
   }
]

[#4]

Correct approc