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

Map JSON Key to Database Columns/Fields

Rising Star

Hello,

I am still a newbie in NiFi. 

I Json flowfile that i want to persist in a database using ConvertJsontoSql & PutSql.

I have no problem if the names of keys in Json flowfile match with the columns name in the database.

But if the keys names are different from column names whilst referring to same thing.

For example:

Flowfile attribute:  surname

Database column: lastname

I have no access to alter database. And i have tried JoltTransform which didn't work.

Please, how do i map json keys to columns names , even if the key names change it would still point to the same column in the database.

Thank you.

Screenshot 2022-02-02 105449.png

 

 

2 REPLIES 2

Explorer

For the JoltTransformation what people will need to see is the data being fed to it in the context of the flow, i.e., the queue prior to the transform contains what? If it's an array then you're transform is wrong but seeing that will hopefully allow people to solve your issue.

 

Regards,

Ron.

Master Collaborator

You can try the following Jolt spec in a JoltTransformRecord processor to change the name of one or more columns:

Jolt Transformation DSL: Chain

Jolt Specification:

[
{
"operation": "shift",
"spec": {
"*": "&",
"surname": "lastname"
}
},
{
"operation": "remove",
"spec": {
"surname": ""
}
}
]

 

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.