Is there a way to dynamically extract the Application Insights telemetry context>custom dimensions json fields without using the index position [3]?

Question

We are trying to read Application Insights telemetry files exported to blob storage using Copy activity with an Azure SQL DB table sink.

Is there a way to dynamically extract the Application Insights telemetry context>custom dimensions json fields without using the index position [3]? The position of "ProductName" property is not fixed.

Here is a sample column mapping:
"source": { "path": "$[''context''][''custom''][''dimensions''][3][''ProductName'']" },
"sink": { "name": "CustomProductName", "type": "String" }

Answer 1

This is for the use case I'm working on. I tried the CollectionReference but these are my findings.

AppInsights Telemetry Custom Dimensions
are json arrays containing multiple single element/property members (different elements/properties per member). For this example, dimensions json array has 4 members but each containing a unique non-repeating "Environment", "ProductName", etc elements/properties. The CollectionReference is only able to extract the first element. It is no longer able to iterate to the other member elements.

"custom": {
  "dimensions": 
  [
    { "AspNetCoreEnvironment": "Development" },
    { "Environment": "Development" },
    { "ProductName": "Pre and Post VQ" },
    { "_MS.ProcessedByMetricExtractors": "(Name:'Requests', Ver:'1.1')" }
  ]
}

The MS examples with CollectionReference work with json arrays containing multiple multi-element/multi-property members (repeating elements/properties per member). Example here, orders json array has 3 members each containing the repeating "prod" and "price" elements/properties. The CollectionReference is able to iterate thru all the elements/properties of the members.

"orders": [
    {
        "prod": "p1",
        "price": 23
    },
    {
        "prod": "p2",
        "price": 13
    },
    {
        "prod": "p3",
        "price": 231
    }
],

Answer 2

Hey, I found it, you need to do $[''context''][''custom''][''dimensions''][0:5][''ProductName'']"