Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

transform ExecuteSQL record into JSON in Apache Nifi

avatar
New Contributor

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

3 REPLIES 3

avatar
Expert Contributor

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 ...

avatar
New Contributor

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
   }
]

 

avatar
Expert Contributor

Correct approc