CODEWITHSUNDEEP
azureazure-data-explorerkql
Naresh Nagpal
Naresh Nagpal

Reputation: 387

Filtering Data in JSON based on value instead of Index - Kusto Query Langauge

I am trying to extract specific field from json by filtering data based on it's value instead of Index.

For example my json looks like below

    "AllData": [
        {
            "ID": "1",
            "Value": "Value1"
        },
        {
            "ID": "2",
            "Value": "Value2"
        },
        {
            "ID": "3",
            "Value": "Value3"
        },
        {
            "ID": "4",
            "Value": "Value4"
        },
        {
            "ID": "5",
            "Value": "Value5"
        }
    ]
}

I need to project section (id and value) where value = valueX. But valueX may not always at index X it can be at any other index also. So while projecting I can not use Index. I need to project based on value. I can use contains operator in my where clause which helps to filter the arrays (list of AllData array) as shown below

MyDataSet
| where parse_json(MyJson) contains("Value5")
| project MyJson[5].ID, MyJson[5].Value // this may give wrong result because Value5 can be at some other index

Any Suggestions will be helpful.

Upvotes: 0

Views: 2306

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you can use mv-apply: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/mv-applyoperator

let my_value = "Value3";
print d = dynamic({"AllData": [
        {
            "ID": "1",
            "Value": "Value1"
        },
        {
            "ID": "2",
            "Value": "Value2"
        },
        {
            "ID": "3",
            "Value": "Value3"
        },
        {
            "ID": "4",
            "Value": "Value4"
        },
        {
            "ID": "5",
            "Value": "Value5"
        }
    ]
})
| mv-apply d = d.AllData on (
    project ID = d.ID, Value = d.Value
    | where Value == my_value
)
ID Value
3 Value3

Upvotes: 2

Related Questions