How to implement JSON based conditions

Question

I'm looking for a technique to apply a condition based on JSON. Look at the sample below where the goal is to filter the records and only pick those with "isDefault=true". Is there a way to do it without using a string search, which appears sloppy.

let T = datatable (id: int) [1, 2, 3];
T 
| extend json = dynamic([{  "capabilities":"None",  "isDefault":false, "isInitial":false,   "name":"contoso.fr", "type":"Managed"},{  "capabilities":"Mail",  "isDefault":true, "isInitial":false,   "name":"login.contoso.com", "type":"Managed"},{  "capabilities":"Mail,Office",  "isDefault":false, "isInitial":false,   "name":"contoso.com", "type":"Managed"}])
| extend data = parse_json(json)
|  project id // isDefault == "true" //want this to be the condition

Answer 1

you could use the mv-apply operator

for example:

print payload = dynamic(
[
    {"capabilities": "None",        "isDefault": false, "isInitial": false, "name": "contoso.fr",        "type": "Managed"},
    {"capabilities": "Mail",        "isDefault": true,  "isInitial": false, "name": "login.contoso.com", "type": "Managed"},
    {"capabilities": "Mail,Office", "isDefault": false, "isInitial": false, "name": "contoso.com",       "type": "Managed"}
])
| mv-apply with_itemindex = i payload on (
    where payload.isDefault == true
)

payload	i
{ "capabilities": "Mail", "isDefault": true, "isInitial": false, "name": "login.contoso.com", "type": "Managed" }	1