Support Questions

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

Converting Keys and values of a flat json to two separate columns in Nifi

avatar
Explorer

Hello,

After flattening a complex nested json to a flat json using the FlattenJson Processor, I want to load all the keys and values of that flat json to two separate columns of a table in database. What is the most efficient way to achieve this in Nifi?

Thanks in advance

@TimothySpann
@mburgess 
@bbende 

4 REPLIES 4

avatar
Master Guru

I would create a schema

then 

PutDatabaseRecord

 

do you have an example of. the output data?

avatar
Master Guru

avatar
Explorer

@TimothySpann As an example you can consider the below example json object,

{
    "car1":"Ford",
    "car2":"BMW",
    "car3":"Fiat",
    "car4":"Mercedes",
    "car5":"Ferrari"
    "car6":"Nissan"
}

Essentially, my table should be having 6 records in total. The values of first column should be car1, car2,........,car6 and the values of second columns should be Ford, BMW, Fiat, Mercedes, Ferrari, Nissan

avatar
Master Guru

What are the column names in your table? Assuming "carId" and "carType", you can use JoltTransformJson or JoltTransformRecord with the following spec:

 

[
{
"operation": "shift",
"spec": {
"*": {
"$": "carId",
"@": "carType"
}
}
},
{
"operation": "shift",
"spec": {
"carId": {
"*": {
"@": "[&0].carId"
}
},
"carType": {
"*": {
"@": "[&0].carType"
}
}
}
}
]