Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Nifi - Merge SQL results as JSON array with Exisitng JSON array.

Highlighted

Nifi - Merge SQL results as JSON array with Exisitng JSON array.

New Contributor

I'm a newbie to Nifi and learning eventually. I'm looking to create a JSON array by utilizing the results from SQL server DB which can return 0-50 records and merge with the existing JSON array. @Matt Burgess


Below is my initial JSON which I retrived from SQL DB and converted to JSON

{

"lastName" : "cgLastName6",

"gender" : "Male",

"currentDate" : "20190515",

unique_id : "1234",

"middleInitial" : "D",

"address_2" : "fake addy 2",

"mailingZipCode" : "55555",

"provider_address_1" : "123 fake addy",

"program_1" : "program_1",

"program_2" : "",

"program_3" : "program_3",

"program_4" : "",

"program_5" : "program_5",

"homeAddress" : "123 fake st",

"primaryLanguage" : "",

"country" : "us",

}


Now, I'm using Execute SQL Processer with query : Select * from test_view where unique_id = "1234";


This can return multiple records anywhere from 0 - 50

Sample Input:

1st Record:

"id": "01",

"make": "bmw",

"model": "550",

"edition": "sports",

"color": "black",

"year": "2019",

phone: "1234567890"

2nd Record:

"id": "02",

"make": "audi",

"model": "A4",

"edition": "turbo",

"color": "red",

"year":"",

"phone": "0123456789"

3rd Record:

"id": "03",

"make": "benz",

"model": "c-lass",

"color": "blue",

"edition": "",

"year": "2019",

phone: ""


Expected Output:


{

"lastName": "cgLastName6",

"gender": "Male",

"currentDate": "20190515",

"unique_id": "1234",

"middleInitial": "D",

"address_2": "fake addy 2",

"mailingZipCode": "55555",

"provider_address_1": "123 fake addy",

"program_1": "program_1",

"program_2": "",

"program_3": "program_3",

"program_4": "",

"program_5": "program_5",

"homeAddress": "123 fake st",

"primaryLanguage": "",

"country": "us",

"cars": [{

"id": "02",

"make": "audi",

"model": "A4",

"edition": "turbo",

"color": "red",

"telephoneNumbers": [{

"extension": "+1",

"prefix": "",

"phoneNumber": "${phone}",

"phoneType": "Home",

"priority": ""

}]

}, {

"id": "01",

"make": "bmw",

"model": "550",

"edition": "sports",

"color": "black",

"year": "2019",

"telephoneNumbers": [{

"extension": "+1",

"prefix": "",

"phoneNumber": "${phone}",

"phoneType": "Home",

"priority": ""

}]

}, {

"id": "03",

"make": "benz",

"model": "c-lass",

"color": "blue",

"year": "2019",

"telephoneNumbers": [{

"extension": "+1",

"prefix": "",

"phoneNumber": "${phone}",

"phoneType": "Home",

"priority": ""

}]


}]

}


In the cars array if any of these id, make, model, color, year fields value is null, those fields to be ignored. Even though phone field returns null, telephoneNumbers array should be constructed with provided arttibute names and null values.


Please do help me.


Thanks in advance