9

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.

enter image description here

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Tedford
  • 2,842
  • 2
  • 35
  • 45

2 Answers2

14

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']
Maxim Kosov
  • 1,930
  • 10
  • 19
1

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"]
EranG
  • 822
  • 4
  • 10
  • unfortunately when I run that query I end up with the same number of result rows with two empty columns named type and count_ – Tedford Jun 27 '17 at 15:44