1

I'm working in Apache NiFi and I've a question: how to handle nested arrays in JSON with QueryRecord processor? For example I've a JSON:

{
   "offerName":"Viatti Strada Asimmetrico V-130 205/55 R16 91V",
   "detailedStats":[
      {
         "type":"mobile",
         "clicks":4,
         "spending":"2.95"
      }
   ]
}

How can I extract array to get the following result:

{
  "offerName": "Viatti Strada Asimmetrico V-130 205/55 R16 91V",
  "type": "mobile",
  "clicks": 4,
  "spending": "2.95"
}

I read about RPATH, but didnt find good examples.

Tried with:

SELECT RPATH(detailedStats, '/detailedStats[1]')
  FROM flowfile

But it throws error. How can i get expected result with RPATH?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
funnelCONN
  • 149
  • 1
  • 11
  • What error do you have? accessor to first element is [0] – daggett Apr 17 '21 at 11:21
  • Tried with [0] also.Error is: `java.io.IOException: org.apache.nifi.processor.exception.ProcessException: Could not determine the Avro Schema to use for writing the content: org.apache.nifi.processor.exception.ProcessException: IOException thrown from QueryRecord[id=276c15fd-19d1-105c-d405-d5bf51052a95]: java.io.IOException: org.apache.nifi.processor.exception.ProcessException: Could not determine the Avro Schema to use for writing the content` – funnelCONN Apr 17 '21 at 12:13

2 Answers2

2

You can select like below via QueryRecord . However it seems you are having an issue while writing. I used JsonRecordSetWriter with Inherent Record Schema. this is a good tutorial If you prefer avro schema

SELECT offerName,
       RPATH_STRING(detailedStats, '/type') type,
       RPATH_INT(detailedStats, '/clicks') clicks,
       RPATH_STRING(detailedStats, '/spending') spending
  FROM FLOWFILE

result is an array, so you should split it with $.* at the downstream

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
yaprak
  • 487
  • 1
  • 3
  • 8
1

An alternative method might be adding a JoltTransformJSON processor with (shift type) specification, which's reached from the Advanced button of Settings tab, with the following code

[
  {
    "operation": "shift",
    "spec": {
      "detailedStats": {
        "*": {
          "@(2,offerName)": "offerName",
          "*": "&"          
        }
      }
    }
  }
]

in order to extract your desired result.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55