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

Catameo, Fred (Taguig City 1634) 236 Reputation points
2021-01-20T15:24:07.703+00:00

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" }

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,741 questions
{count} vote

2 answers

Sort by: Most helpful
  1. Catameo, Fred (Taguig City 1634) 236 Reputation points
    2021-01-22T22:32:08.383+00:00

    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
        }
    ],
    

  2. Manisha Barnwal 1 Reputation point
    2021-11-11T16:51:23.513+00:00

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