Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

appending a string to json based on Guid

Copper Contributor

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?

2 Replies
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?

Hi@Nikhil_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