Apache NiFi QueryRecord Get record based on max value between two columns
Hi community,
hope someone can help me on below.
My Flow File content is something like this:
[ {
"version" : "5",
"shipment_number" : "2022065597",
"situation_code" : "MLV",
"justification_code" : "CFM",
"situation_date" : "202206071253",
"new_appointment_date" : "202301081253",
}, {
"version" : "5",
"shipment_number" : "2022065597",
"situation_code" : "MLV",
"justification_code" : "CFM",
"situation_date" : "202206071252",
"new_appointment_date" : "",
} ]
I need to add a Query Record processor in order to grab only one JSON record for each Flow File.
The condition is: Get a single result with highest value of situation_date or new_appointment_date.
Just to explain better, the logic behind should be the following:
Grab the max of situation_date, the max of new_appointment_date and compare each other, then take the highest of the comparison and get entire JSON record.
In MYSQL or T-SQL it could be easier, but if i understand well Query Record is based on Apache Calcite which is not so easy for me.
Does anyone can point mee on the right direction?
Appreciate

