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.

JSON file with operation values ADD/MODIFY based on this I need to send JSON data to INSERT or UPDATE

Solved Go to solution

JSON file with operation values ADD/MODIFY based on this I need to send JSON data to INSERT or UPDATE

Explorer

My file:

"ws:Operation": "ADD",
"ws:Supervisor_Name": {
"ws:PriorValue": "",
"content": "Dr. Leah Thaler"

 

In above example i need to send the content:Dr. Leah Thaler to insert 

My file:

"ws:Operation": "MODIFY",
"ws:Supervisor_Name": {
"ws:PriorValue": "",
"content": "Dr. Leah Thal"

In above example i need to send the content:Dr. Leah Thaler to update.

Database is Oracle - the flow: Getfile-> EvaluateJSONPath -> send the data to two tables using AttributesToJSON for flatten JSON -> ConvertJSONSQL to Putsql. 

 

Note: I dont want to write AVRO schema. So we build this flow 

2020-03-23 09_48_22-NiFi Flow.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Re: JSON file with operation values ADD/MODIFY based on this I need to send JSON data to INSERT or UPDATE

Master Collaborator

After you get all the attributes you want,  you check them with RouteOnAttribute.  In that proc click + and set update / inserts equal to matches you want.  You use regex in the value for each. For example insert  is ${operation.equals('add')}. After RouteOnAttribute is configured, drag update to the update query proc, and insert to the insert query proc.

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven

View solution in original post

Highlighted

Re: JSON file with operation values ADD/MODIFY based on this I need to send JSON data to INSERT or UPDATE

Explorer

Thanks @stevenmatison . worked perfectly.. 

View solution in original post

2 REPLIES 2
Highlighted

Re: JSON file with operation values ADD/MODIFY based on this I need to send JSON data to INSERT or UPDATE

Master Collaborator

After you get all the attributes you want,  you check them with RouteOnAttribute.  In that proc click + and set update / inserts equal to matches you want.  You use regex in the value for each. For example insert  is ${operation.equals('add')}. After RouteOnAttribute is configured, drag update to the update query proc, and insert to the insert query proc.

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven

View solution in original post

Highlighted

Re: JSON file with operation values ADD/MODIFY based on this I need to send JSON data to INSERT or UPDATE

Explorer

Thanks @stevenmatison . worked perfectly.. 

View solution in original post

Don't have an account?
Coming from Hortonworks? Activate your account here