azure-data-explorerkqlkusto-exploreraskql

How to implement JSON based conditions


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

Solution

  • 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