Support Questions
Find answers, ask questions, and share your expertise

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

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

Super Guru

I would create a schema

then 

PutDatabaseRecord

 

do you have an example of. the output data?

Super Guru

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

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