Forum Discussion

Nikhil_Babu_Battula's avatar
Nikhil_Babu_Battula
Copper Contributor
Sep 29, 2023

appending a string to json based on Guid

Hi Team,
I have the json data like below

Properties column

{"State":"Received","NotificationId":"abc"}
{"State":"Queued","NotificationId":"abc"}
{"State":"Published","NotificationId":"abc", "Short": "tim"}

{"State":"Received","NotificationId":"def"}
{"State":"Queued","NotificationId":"def"}
{"State":"Published","NotificationId":"def", "Short": "mit"}

Using parse_json() i managed to parse it and extarcted the info from it. I am trying to add Short value ("Short": "tim") to other json values based on NotificationId ("NotificationId":"abc") . Tried different functions. But so far no luck. could u guys please guide me on this?

KQL
kusto
  • GBushey's avatar
    GBushey
    Icon for Microsoft rankMicrosoft
    Oct 03, 2023
    You can use the "make_bag()" function but that only works with a summary. Is there any reason you just could not create a new column that holds the value?
  • rutgersmeets's avatar
    rutgersmeets
    Brass Contributor
    Oct 15, 2023

    HiNikhil_Babu_Battula 

     

    bag_set_key() will be useful here.

     

    Assuming that the column holding you JSON is called Properties and is of string type, you can use this query:

     

    Table

    | extend Properties = parse_json(Properties) // You can skip this if Properties is already of Dynamic type.

    | extend Properties = iff(Properties.NotificationId == "abc", bag_set_key(Properties, "Short", "tim"), Properties)

     

    This will set the value of Properties.Short to "tim" if Properties.NotificationId is equal to "abc". If NotificationId is something else, it will leave Properties as is.

     

    Let me know if this helped!

     

    Rutger

     

Resources