topic Re: Split nested JSON in Cortex XDR Discussions

Split nested JSON

D.Demarest — Thu, 17 Oct 2024 16:15:18 GMT

I have a field named "ModifiedProperties" and it has values like this below, I cant for the life of me figure out how XQL splits these up, Splunk uses SPAN or MVexpand and it works like a champ but i cant figure out what function does the same thing in XQL. THANK YOU!

[

{

"Name": "StrongAuthenticationRequirement",

"NewValue": "[]",

"OldValue": "[\r\n {\r\n \"RelyingParty\": \"*\",\r\n \"State\": 0,\r\n \"RememberDevicesNotIssuedBefore\": \"2017-10-13T16:59:21+00:00\",\r\n \"ConfigStore\": null\r\n }\r\n]"

{

"Name": "Included Updated Properties",

"NewValue": "StrongAuthenticationRequirement",

"OldValue": ""

{

"Name": "TargetId.UserType",

"NewValue": "Member",

"OldValue": ""

}

]

Re: Split nested JSON

Fm12345 — Fri, 18 Oct 2024 18:44:20 GMT

Which key are you trying to extract?

For e.g: we can extract name key from all jsons within the array as below.

alter namearr=arraymap (modifiedproperties,"@element"->Name)

|Arrayexpand namearr.

This way we will get the Name key expanded.

Re: Split nested JSON

justin_smi — Tue, 22 Apr 2025 12:46:45 GMT

HI @D.Demarest, I am having the exact same issue with the same field. Were you ever able to figure out a solution to this?

Re: Split nested JSON

D.Demarest — Tue, 22 Apr 2025 14:13:47 GMT

hi @justin_smi ,

So the way i got it to work was something like this:

|alter newgroup = json_extract(targetResources, "$[0].modifiedProperties[1].newValue")

targetResources is the field name, then its the zero index plus the parent parent key/pair then you use the index of the field you want out