Check if key of dictionary exists in a specific column in Kusto?

Question

I got the following dataset:

let data = datatable(Timestamp:datetime, Name:string, Value:int)
            [
             datetime(2022-02-18 10:00:00 AM), "AX_100A_A00", 100,
             datetime(2022-02-18 10:01:00 AM), "BX_101B_B00", 200,
             datetime(2022-02-18 10:02:00 AM), "CX_102C_C00", 300,
             datetime(2022-02-18 10:03:00 AM), "DX_103D_D00", 400,  
             datetime(2022-02-18 10:04:00 AM), "EX_104E_E00", 500,
            ];
    let mydict = dynamic(
        {
             "100A":"New York"
            ,"101B":"Geneva"
            ,"102C":"France"
            ,"103D":"US"
            ,"104E":"Canada"
        }
    );
    data
    | summarize result = max(Value) by Floor_Name = tostring(mydict[substring(Name, 3, 4)])

To illustrate what I am trying achieve here. Between the two underscores there is a code which represents a specific Location.

My question here, is how is it possible to add a condition which checks if the word between the 2 underscores doesn't exist as key in the dictionary, then just display the value between the 2 underscores. However if it does exist in the dictionary, then display its friendly name. Let us assume that a new name was added FX_105F_F00. In this case, since it is not found in the dictionary, then no friendly name should be displayed. It will be displayed as it is. An iff condition should be added to the floor name in the code but how should the syntax be ?

user_6336479 · Answer

Few things to note here:

It is valid to address non-existing key within a JSON document.
The returned result in that case would be null.
The string data type doesn't support null values.
Using tostring() on null value would return an empty string.
coalesce() does work for empty string, the same way it works for null values of other data types.

So, for 105F, mydict["105F"] is null and tostring(mydict["105F"]) is an empty string, so coalesce proceed to the 2nd value (Floor_Code).

let data = datatable(Timestamp:datetime, Name:string, Value:int)
            [
             datetime(2022-02-18 10:00:00 AM), "AX_100A_A00", 100,
             datetime(2022-02-18 10:01:00 AM), "BX_101B_B00", 200,
             datetime(2022-02-18 10:02:00 AM), "CX_102C_C00", 300,
             datetime(2022-02-18 10:03:00 AM), "DX_103D_D00", 400,  
             datetime(2022-02-18 10:04:00 AM), "EX_104E_E00", 500,
             datetime(2022-02-18 10:05:00 AM), "FX_105F_F00", 600
            ];
    let mydict = dynamic(
        {
             "100A":"New York"
            ,"101B":"Geneva"
            ,"102C":"France"
            ,"103D":"US"
            ,"104E":"Canada"
        }
    );
    data
    | extend Floor_Code = substring(Name, 3, 4)
    | summarize result = max(Value) by Floor_Name = coalesce(tostring(mydict[Floor_Code]), Floor_Code)

Floor_Name	result
New York	100
Geneva	200
France	300
US	400
Canada	500
105F	600

Fiddle

Check if key of dictionary exists in a specific column in Kusto?

1 Answers