question Re: Using RPATH on nested list in SQL in QueryRecord in NiFi in Support Questions

Using RPATH on nested list in SQL in QueryRecord in NiFi

NidhiPal09 — Tue, 25 Jun 2024 12:44:05 GMT

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.

Re: Using RPATH on nested list in SQL in QueryRecord in NiFi

DianaTorres — Tue, 25 Jun 2024 19:23:32 GMT

@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.

Re: Using RPATH on nested list in SQL in QueryRecord in NiFi

saquibsk — Thu, 27 Jun 2024 11:53:43 GMT

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.

Re: Using RPATH on nested list in SQL in QueryRecord in NiFi

DianaTorres — Mon, 01 Jul 2024 17:27:54 GMT

@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.