Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Using RPATH on nested list in SQL in QueryRecord in NiFi

avatar
New Contributor

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.

3 REPLIES 3

avatar
Community Manager

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


Regards,

Diana Torres,
Community Moderator


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community:

avatar
Expert Contributor
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.
Shakib M.

avatar
Community Manager

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


Regards,

Diana Torres,
Community Moderator


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community: