How to parse a custom key/value pair string into a JSON object in Azure Data Explorer?

Question

I needed to parse a string of properties to a JSON object. Here is the input format:

"var1=[val1] & var2=[val2] & var3=[val3] & var4=[val4]"

And the desired output:

{
   "var1": "val1",
   "var2": "val2",
   "var3": "val3",
   "var4": "val4"
}

I have the answer, see below...

Yoni L. · Accepted Answer · 2020-02-17 18:32:29Z

2

an alternative method would be using extract_all(), then 'converting' the array into a property bad using pack() and make_bag():

print str = "var1=[val1] & var2=[val2] & var3=[val3] & var4=[val4]"
| project Properties = extract_all(@"(?P<key>\w+)?=\[(?P<value>.*?)\]", dynamic(["key","value"]), str)
| mv-apply Properties on (
    summarize make_bag(pack(tostring(Properties[0]), Properties[1]))
)

answered Feb 17, 2020 at 18:32

Yoni L.

26.4k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yoni L. Over a year ago

that's usually better tested on a real and meaningful data set - you can run both N times against the same data set, then look at resource utilization (CPU, memory) and total duration using .show queries

nrofis · Accepted Answer · 2022-12-19 15:06:40Z

1

Thanks Ziad Hammoud for providing this cool workaround!

print str = "var1=[val1] & var2=[val2] & var3=[val3] & var4=[val4]"
| extend str = replace("\\] & ", "&", replace("=\\[", "=", str))
| project Properties = parse_urlquery(str)['Query Parameters']
| where Properties.var2== "val2"

edited Dec 19, 2022 at 15:06

nrofis

9,91717 gold badges71 silver badges126 bronze badges

answered Feb 17, 2020 at 10:11

Yoram Singer

231 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to parse a custom key/value pair string into a JSON object in Azure Data Explorer?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related