Forum Discussion
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?
- GBusheyMicrosoftYou 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?
- rutgersmeetsBrass Contributor
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