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

Question

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.

Yoni L. · Accepted Answer · 2021-05-13 21:39:37Z

2

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

answered May 13, 2021 at 21:39

Yoni L.

26.5k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naresh Nagpal Over a year ago

Thanks it worked. The only difference is I had my Json in another variable so I could not use dynamic because that was not accepting a predefined variable. So I used pack() function - pack("d",MyJsonVariable),

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related