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

Question

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

Answer 1

@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

• RE2 syntax for KQL
• RE2 library

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:

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.