How can I extract the list of value for a given property in an array JSON objects using regex?

Question

I have the following JSON payload in a column in an ADX cluster that I need extract data from.

[
   {
      "RailType":"HE400",
      "InstallationDate":"2017-07-19T16:00:00.0000000Z",
      "CurvePercentage":99.61377338837824
   },
   {
      "RailType":"ONEHH",
      "InstallationDate":"2017-05-21T16:00:00.0000000Z",
      "CurvePercentage":0.3862266116217716
   }
]

What I've come up with so far (play with it on RegExr):

"RailType":(".*?")

Unfortunately, this matches:

"RailType":"HE400"
"RailType":"ONEHH"

When what I really want is simply:

HE400
ONEHH

I know it is not ideal to parse JSON with regex, but I am constrained by ADX querying requirements to use regex for this task.

score 0 · Answer 1 · answered Jun 30 '23 at 01:29

Here's an example pattern that matches and captures the values of the "RailType" field:

"RailType":"(.*?)"

This pattern uses a non-greedy match (.*?) within capturing parentheses "" to extract the value between the double quotes. By referencing the captured group, you can obtain the desired values without the surrounding quotes.

Here's an example of how you can apply this regex pattern in ADX to extract the "RailType" values:

CurvesWearRate
| project RailType = extract("\"RailType\":\"(.*?)\"", 1, tostring(RailTypes))
| where isnotempty(RailType)
| distinct RailType

This query extracts the "RailType" values using the modified regex pattern and assigns them to a new column called "RailType". The extract operator is used to capture the desired values from the JSON payload.

After executing the query, you should obtain the following result:

| RailType |
|----------|
| HE400    |
| ONEHH    |

How can I extract the list of value for a given property in an array JSON objects using regex?

1 Answers1