CODEWITHSUNDEEP
azure-data-explorer
Gregory Suvalian
Gregory Suvalian

Reputation: 3832

How do I parse complex json object in Azure Data Explorer

I have following property which I need to parse as JSON. I tried to use parse_json() but it does not work

Query

AzureActivity
| where OperationNameValue == "Microsoft.Authorization/roleAssignments/write" 
| where ActivityStatus  == "Started"
| where (Properties contains "8e3af657-a8ff-443c-a75c-2fe8c4bcb635") or (Properties contains "b24988ac-6180-42a0-ab88-20f7382dd24c")
| extend request = parse_json(Properties)
| where request.requestbody.Properties.Scope == "/subscriptions/6f5c5be9-a2dd-49c9-bfa1-77d4db790171"

Raw data which needs to be parsed

{ "requestbody": "{\"Id\":\"992a2739-9bd2-4d04-bc5f-5ed1142b9861\",\"Properties\":{\"PrincipalId\":\"5ac319a4-740b-4f09-9fd3-fce3ce91fedf\",\"RoleDefinitionId\":\"/subscriptions/6f5c5be9-a2dd-49c9-bfa1-77d4db790171/providers/Microsoft.Authorization/roleDefinitions/8e3af657-a8ff-443c-a75c-2fe8c4bcb635\",\"Scope\":\"/subscriptions/6f5c5be9-a2dd-49c9-bfa1-77d4db790171\"}}" }

Upvotes: 1

Views: 5650

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

have a look at the bottom of this page (also quoted below), which explains why the following works (BTW, note that I've replaced contains with has for you, from an efficiency standpoint):

AzureActivity
| where OperationNameValue == "Microsoft.Authorization/roleAssignments/write" 
| where ActivityStatus  == "Started"
| where (Properties has "8e3af657-a8ff-443c-a75c-2fe8c4bcb635") or (Properties has "b24988ac-6180-42a0-ab88-20f7382dd24c")
| extend request = parse_json(tostring(parse_json(Properties).requestbody))
| project request.Properties.Scope

It is somewhat common to have a JSON string describing a property bag in which one of the "slots" is another JSON string.

For example: let d='{"a":123, "b":"{\\"c\\":456}"}'; print d

In such cases, it is not only necessary to invoke parse_json twice, but also to make sure that in the second call, tostring will be used. Otherwise, the second call to parse_json will simply pass-on the input to the output as-is, because its declared type is dynamic:

let d='{"a":123, "b":"{\\"c\\":456}"}'; print d_b_c=parse_json(tostring(parse_json(d).b)).c

Upvotes: 5

Related Questions