KQL - Extract with RegEx Error "Relop semantic error: SEM0420: Regex pattern is ill-formed"

Manikanth Ramagani 21 Reputation points
2023-05-05T20:52:45.8+00:00

I am trying to extract matches/attribue values using a regular expression from a nested JSON using extract and RegEx in KQL query

Simplified sample text /JSON is

{
  "FunctionalArea": null,
  "Interface": null,
  "Name":"abc",
  "failedRequests": null,
  "requests": 23,
  "childRows": [
    {
      "Name":"abc-123",
"childRows": [
    {
      "Name":"abc-123-123",
     },
    {
      "Name":"abc-123-456",
    }
  ]
     },
    {
      "Name":"abc-456",
"childRows": [
    {
      "Name":"abc-456-123",
     },
    {
      "Name":"abc-456-456",
    }
  ]
    }
  ]
}

KQL query

let level2Result = dynamic({selectedrow});
print level2Result
|project Names = extract(@'(?<="Name":")(?:"|[^"])*',2,level2Result,typeof(string))

Though the Regex works in https://regex101.com/, its failing in KQL with error Relop semantic error: SEM0420: Regex pattern is ill-formed: (?<="Name":")(?:"|[^"])*... I was expecting to extract below values. Please assist with this. Note that I tried with escape charecters and changine ' to ". But that didnt work too. Though mv-expand and bag_unpack are working, they are taking 4-5 seconds in nested JSON scenario for actual request and hence looking for options via regular expression.

abc
abc-123
abc-123-123
abc-123-456
abc-456
abc-456-123
abc-456-456
Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
3,013 questions
Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
506 questions
0 comments No comments
{count} votes

Accepted answer
  1. AnuragSingh-MSFT 21,241 Reputation points
    2023-05-08T12:16:27.6466667+00:00

    @Manikanth Ramagani , thank you for posting this question on Q&A.

    I see that you are trying to extract the values of all "Names" from the provided string (a JSON string). Regarding the error that you are getting (SEM0420: Regex pattern is ill-formed:), is because KQL uses RE2 syntax which does not support lookbehind - ?<=. The following link has details of supported syntax

    For the end result, that you are trying to achieve, the extract_all() KQL function with supported RE2 Syntax can easily help. Here is the sample:

    let str = '{  "FunctionalArea": null,  "Interface": null,  "Name":"abc",  "failedRequests": null,  "requests": 23,  "childRows": [    {      "Name":"abc-123","childRows": [    {      "Name":"abc-123-123",     },    {      "Name":"abc-123-456",    }  ]     },    {      "Name":"abc-456","childRows": [    {      "Name":"abc-456-123",     },    {      "Name":"abc-456-456",    }  ]    }  ]}'; //-------- the string provided in question/
    print str
    |project Names = extract_all(@'("Name":"{1}[^"]*.)',str)     //-----extract the exact match
    | mv-expand Names
    | extend split_Name = split(Names, '"')[-2]         //--------split and index
    
    

    The result looks like below:

    User's image

    Hope this helps.

    If the answer did not help, please add more context/follow-up question for it, and we will help you out. Else, if the answer helped, please click Accept answer so that it can help others in the community looking for help on similar topics.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful