How to use mvexpand on an json array of key/value pairs

Question

I have a custom property in my appInsights telemetry that is a json array of a key/value pairs. What I want to do is project out that key/value pair and it seems that using parsejson and mvexpand together is how to achieve this; however, I seem to be missing something. The end result of my expression is a column named type that is the raw json. Attempting to add any property to the expression results in an empty column.

Json encoded property

[{"type":"text/xml","count":1}]

AIQL

requests 
 | project customDimensions 
 | extend type=parsejson(customDimensions.['Media Types'])
 | mvexpand bagexpansion=array type 

Update 6/30/17

To answer EranG's question the output of my request when projecting out the properties as columns is as shown below.

Answer 1

I had the same issue recently. Probably your property already of type dynamic, but its dynamic String not the array. parsejson don't work because it converts String to dynamic, not dynamic to another dynamic. To work around this I suggest you to try first convert your property to String and then parse it again.

Please, try following example. It may help you as it helped me:

requests 
| project customDimensions 
| extend type=parsejson(tostring(customDimensions.['Media Types']))
| mvexpand type
| project type.type, type.['count']

Answer 2

What mvexpand does is to take your array and break it down to lines, so each line will have a single item from the array. If you want to break each item to columns, you'll need to try something like:

requests 
| project customDimensions 
| extend type=parsejson(customDimensions.['Media Types'])
| mvexpand bagexpansion=array type 
| project type = type.type, count_ = type["count"]