Extract Json key, run Sql function and insert result back into Json tree

[#1]

I have extracted below json from my executesql processor:

{
"Id" : "B2606868-F4BC-4230-9749-A083F6D222B2",
"ReminderPlan" : "-5d, -1d'",
"ModifiedOn" : "2024-01-03 19:56:59.0000000"
}
and I have several of these. The task at my hand is to extract the reminderplan value and pass it as an attribute to some sql processor and run a sql function on that reminderplan column value; for example: 

DECLARE @output varchar(255);
SET @output = NewDemo.dbo.StandardizeReminderPlan('-5d, -1d')
SELECT @output AS Result;


After storing output value in Result, I want to insert the function value back in the same json. Could anyone suggest which processors I should use to accomplish this?

[#2]

Any feedback would be appreciated. 

[#3]

Hi @Kiranq ,

This a basic case of data enrichment and there are multiple ways how you can handle this. If for example instead of the function you have a table with a unique id then you can use LookupRecord with DatabaseRecordLookupService or even SimpleDatabaseLookupService . All you have to do here is basically specify the path where you want the new value to be inserted as dynamic property. However since you have a function instead of simple table\view , then the second option is to use ForkEnrich\JoinEnrich processors . The nice thing about those processors is that you have multiple strategies how you can join the data together as you can read here:

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.20.0/org.apach...

Let me know which one works for you better and let me know if you face any issues regarding using either.

If you find this is helpful please accept solution

Thanks