Using RPATH on nested list in SQL in QueryRecord in NiFi

[#1]

I have a json

[
  {
    "orderId": "100",
    "orderItems": [
      {
        "orderItemSeqId": "101",
        "externalId": "56789"
      },
      {
        "orderItemSeqId": "102",
        "externalId": null
      }
    ]
  },
  {
    "orderId": "200",
    "orderItems": [
      {
        "orderItemSeqId": "201",
        "externalId": "78990"
      },
      {
        "orderItemSeqId": "202",
        "externalId": "83937"
      }
    ]
  }
]

Using QueryRecord processor in NiFi, I wish to select the orders having any item with null externalId.

So, in the output we should have only the first map with "100" orderId as it has one item with null externalId.

I tried this SQL with RPATH but doesn't work:

SELECT * 
FROM flowfile 
WHERE RPATH_STRING(orderItems, '[*][isEmpty(./externalId)]')  = 'true'

isEmpty function of RecordPath returns true if the string is empty or null else false.

[#2]

@NidhiPal09 Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our NiFi experts @MattWho @SAMSAL  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

[#3]

Option 2: You can use the RouteOnAttribute processor to filter out FlowFiles with null values using the expression language ${externalId:isNull()} and route them to the next processor.

[#4]

@NidhiPal09 Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.  Thanks.