Can't extract json data

[edfarrera]

I have the following query in app insights to get some information

traces
| where operation_id == "some-operation-id"
| project parse_json(substring(message, 229, (strlen(message) - 258)));

Which seems to be parsing correctly because because after expanding the results everything is separated nicely by key, but if I try to extract some information like the userId by doing this:

traces
| where operation_id == "some-operation-id"
| project parse_json(substring(message, 229, (strlen(message) - 258)))["userId"]

I don't get anything, just empty rows. I have tried different ways to extract it like using .userId, [userId], etc but they are not working. 

What am I doing wrong?

Here is an example of the data that I get from the query:

[#2]

let dataIn='{"userId":"123","siteId":"974"}';
print parse_json(dataIn).userId

[#3 · Accepted solution]

@CliveWatson 

Thanks for the answer. While your example didn't help me solve the problem directly it helped me reach a solution by myself. I was curios why your example worked without issues while mine wasn't so I keep trying. I was having problem in two scenarios:

1.- While using the split function.

2.- Only sometimes while using the substring function.

The solution for the first scenario was just using the tostring() function to transform the extracted item (I don't get this one as the result should already be a string) and then using the parse_json.

As for the second scenario it seams that sometimes my desired string that i wanted to use as json contained escape characters ( using the \ in order to put quotation marks and stuff like that). At first I didn't thought this was an issue because after I used the the parse_json function, the ui results showed me a dynamic object, so I thought that the reason why I couldn't access the values with the key was something else. What I did at the end in order to solve my issue was using first the parse_json function to the result of the substring function, this will clean the string but not make it a json yet, then I will use the tostring() function to convert it again into a string but now it's clean, and then user the parse_json() again to finally have a usable json. I have no idea why I need to do this steps but they work.

To sum up everything. I think that the azure logs ui is transforming the printed data to make it more legible to the users, but because of that I wan't able to realize that my data was not good to convert to json or to extract values from it.