Get only element based on a single parameter

[#1]

Hi guys,

hope anyone can point me on the right direction.

I have the following flow file:

[ {
  "version" : "5",
  "type" : "REPORT",
  "dp_code" : "DHA592",
  "field4" : "83482227299911",
  "field5" : "83482227299911",
  "platform_name" : "GHESTEM LA CHAPELLE",
  "field7" : "01143200",
  "field8" : "72801371220938",
  "field9" : "72801371220938",
  "carrier_name" : "",
  "shipper_name" : "",
  "field12" : "",
  "consignee_name" : "",
  "consignee_code" : "",
  "consignee_address" : "",
  "consignee_city" : "",
  "field17" : "",
  "ldc_number" : "",
  "ldc_date" : "",
  "shipment_number" : "2022115144",
  "shipment_code" : "2022115144/SH",
  "field22" : "",
  "field23" : "",
  "field24" : "",
  "field25" : "liv.cfm",
  "situation_code" : "LIV",
  "justification_code" : "CFM",
  "situation_date" : "20221003",
  "feedback_date" : "202210061222",
  "anomaly_packages_number" : "",
  "comments" : "",
  "new_appointment_date" : "",
  "field33" : "",
  "field34" : "",
  "field35" : "",
  "assigned" : "",
  "field37" : "",
  "agreed_tax" : "",
  "field39" : "",
  "field40" : "20221006123304-3f621cf7-3",
  "field41" : "",
  "field42" : "",
  "field43" : "",
  "field44" : "212059",
  "doc_url" : "",
  "field46" : "",
  "field47" : "",
  "field48" : ""
}, {
  "version" : "5",
  "type" : "REPORT",
  "dp_code" : "DHA592",
  "field4" : "83482227299911",
  "field5" : "83482227299911",
  "platform_name" : "GHESTEM LA CHAPELLE",
  "field7" : "01143200",
  "field8" : "72801371220938",
  "field9" : "72801371220938",
  "carrier_name" : "",
  "shipper_name" : "",
  "field12" : "",
  "consignee_name" : "",
  "consignee_code" : "",
  "consignee_address" : "",
  "consignee_city" : "",
  "field17" : "",
  "ldc_number" : "",
  "ldc_date" : "",
  "shipment_number" : "2022115144",
  "shipment_code" : "2022115144/SH",
  "field22" : "",
  "field23" : "",
  "field24" : "",
  "field25" : "",
  "situation_code" : "POD",
  "justification_code" : "CFM",
  "situation_date" : "20221003",
  "feedback_date" : "202210061208",
  "anomaly_packages_number" : "",
  "comments" : "",
  "new_appointment_date" : "",
  "field33" : "",
  "field34" : "",
  "field35" : "",
  "assigned" : "",
  "field37" : "",
  "agreed_tax" : "",
  "field39" : "",
  "field40" : "20221006121304-3f61f8b6-3",
  "field41" : "",
  "field42" : "",
  "field43" : "",
  "field44" : "212059",
  "doc_url" : "",
  "field46" : "",
  "field47" : "",
  "field48" : "S"
}, {
  "version" : "5",
  "type" : "REPORT",
  "dp_code" : "DHA592",
  "field4" : "83482227299911",
  "field5" : "83482227299911",
  "platform_name" : "GHESTEM LA CHAPELLE",
  "field7" : "01143200",
  "field8" : "72801371220938",
  "field9" : "72801371220938",
  "carrier_name" : "",
  "shipper_name" : "",
  "field12" : "",
  "consignee_name" : "",
  "consignee_code" : "",
  "consignee_address" : "",
  "consignee_city" : "",
  "field17" : "",
  "ldc_number" : "",
  "ldc_date" : "",
  "shipment_number" : "2022115144",
  "shipment_code" : "2022115144/SH",
  "field22" : "",
  "field23" : "",
  "field24" : "",
  "field25" : "",
  "situation_code" : "AAR",
  "justification_code" : "CFM",
  "situation_date" : "20221003",
  "feedback_date" : "202210061208",
  "anomaly_packages_number" : "",
  "comments" : "",
  "new_appointment_date" : "",
  "field33" : "",
  "field34" : "",
  "field35" : "",
  "assigned" : "",
  "field37" : "",
  "agreed_tax" : "",
  "field39" : "",
  "field40" : "20221006121304-3f61f8b6-3",
  "field41" : "",
  "field42" : "",
  "field43" : "",
  "field44" : "212059",
  "doc_url" : "",
  "field46" : "",
  "field47" : "",
  "field48" : "S"
} ]

My need is to grab only one element of json file based on "situation_code" in a priority order.

To explain me well, situation code can have the following possible values:

• POD (search priority 1)
• LIV (search priority 2)
• AAR (search priority 3)
• TTR (search priority 4)

The logic behind should be the following:

Grab only one entire element by evaluating at first POD as value, if found grab it, if not found then evaluate LIV and grab it in case, if not found then evaluate AAR and grab it, and so on until you found a possible value.

Many thanks for any hints

[#2]

I believe you could use QueryRecord to take you in the right direction. 

You could define properties to route files based on their situation code, for example:

Key: POD

Value: SELECT * FROM flowfile WHERE  situation_code = 'POD' LIMIT 1

This would route 1 element in your input array where the code is POD to the new relationship "POD" (same as the dynamic property name).

This doesn't exactly match your case of retrieving an element only if higher priority codes weren't found, but perhaps you could use this example to get closer to a solution. Good luck! 

[#3]

Thanks for the update, however that's is the problem.

The Query Record does not fit my needs cause cannot implement the logic.

Note that the json doesn't have always all situation codes, so the pseudo code should be:

Grab the element with sit_code = POD only if present, otherwise go further with others.

Any help is appreciate.

Many Thanks

[#4 · Accepted solution]

Perhaps you can use the processor CalculateRecordStats to count how many
records of each situation code you have in a given file, then once that is
put in your attributes you can try a couple of different methods to decide
what to do, for example you can use RouteOnAttribute and then check if
attribute sit_code.pod>0 then send to extract_one_pod_element and there you
could use QueryRecord to take out exactly one POD element, otherwise route
to LIV, AAR, etc.

[#5]

Many thanks,

it works like a charm!