Microsoft Azure Time Series Insights: How to query a flattened dynamic array

Question

We use Time Series Insights Gen 2 that reads data from the IoT Hub. The data has a very similar form as Example C in this documentation: https://docs.microsoft.com/en-us/azure/time-series-insights/concepts-json-flattening-escaping-rules

Our data has the following structure:

{
  "timestamp": "2020-11-01T10:00:00.000Z",
  "sensors": [{
        "name" : "temperature",
        "unit" : "celsius"
        "value": 25.39
    },
    {
        "name" : "humidity",
        "unit" : "percentage" 
        "value": 97.85
    }
  ]
}

And usuall with many more 'sensor' items in the array. We always used the Time Series Insights PAYG Preview, and it worked fine. We could query the temperature with the following JSON payload:

{
  "getEvents": {
    "timeSeriesId": ["some Id"],
    "searchSpan": {
        "from": "2020-08-27T07:34:00.000Z",
        "to": "2020-08-27T07:34:10.000Z"
    },
    "filter": {
        "tsx": "$event.sensors_name.String = 'temperature'"
    },
    "projectedProperties": [{
            "name": "sensors_value",
            "type": "Double"
        }]
    }
}

This worked perfectly, until Microsoft bumped the PREVIEW PAYG version towards an official release. It doesn't work anymore, and we've found this in the documentation:

source: https://docs.microsoft.com/en-us/azure/time-series-insights/concepts-supported-data-types

The following documentation even clearly states that the flattening of arrays are different now:

source: https://docs.microsoft.com/en-us/azure/time-series-insights/ingestion-rules-update

Is there a possibility to still query this dynamic type of array, without having to put the timestamp or deviceId in each array item?

ranah ranah · Accepted Answer · 2020-08-27T19:15:40

That functionality is not yet there, dynamic types can't be referenced in TSX expressions and the values within the array cannot be retrieved as projectedProperties. The entire array would have to be retrieved and then parsed client-side. To trigger flattening you'd have to add an ID or timestamp within the objects as you mention above.

One idea--create a new TSI environment and configure a composite TS ID--whatever your initial ID is plus sensor.name, (assuming that name came be a unique identifier), then you'd also take away the need for the filter for temp.

Microsoft Azure Time Series Insights: How to query a flattened dynamic array

1 Answers