Azure stream analytics job using arrays

Question

I set up a job in stream analytics that takes messages from a IotHub and loads the values in a SQL table.

Everything worked fine when using only one set of values per message, however I wanted to send several sets of values in a single message using an array.

The following is an example of a Json message that reaches the job:

[{
    "valores": {
        "0": {
            "voltage": 230.8,
            "current": 0.18,
            "power": 32.093,
            "frequency": 50,
            "energy": 0.71,
            "dvcid": 1,
            "gway": "SPC-G02",
            "time": "2018-12-27T16:02:20.1690000Z"
        },
        "1": {
            "voltage": 230.7,
            "current": 0.144,
            "power": 23.759,
            "frequency": 50.1,
            "energy": 0.71,
            "dvcid": 1,
            "gway": "SPC-G02",
            "time": "2018-12-27T16:02:25.1690000Z"
        },
        "2": {
            "voltage": 230.7,
            "current": 0.143,
            "power": 23.369,
            "frequency": 50,
            "energy": 0.71,
            "dvcid": 1,
            "gway": "SPC-G02",
            "time": "2018-12-27T16:02:30.1740000Z"
        },
        "3": {
            "voltage": 230.4,
            "current": 0.163,
            "power": 28.075,
            "frequency": 50,
            "energy": 0.71,
            "dvcid": 1,
            "gway": "SPC-G02",
            "time": "2018-12-27T16:02:35.1730000Z"
        },
        "4": {
            "voltage": 230.5,
            "current": 0.167,
            "power": 29.207,
            "frequency": 50,
            "energy": 0.71,
            "dvcid": 1,
            "gway": "SPC-G02",
            "time": "2018-12-27T16:02:40.1810000Z"
        },
    },
    "EventProcessedUtcTime": "2018-12-27T16:03:00.9574234Z",
    "PartitionId": 3,
    "EventEnqueuedUtcTime": "2018-12-27T16:02:58.0290000Z",
    "IoTHub": {
        "MessageId": null,
        "CorrelationId": null,
        "ConnectionDeviceId": "RaspberryPi-Teste1",
        "ConnectionDeviceGenerationId": "636807313519039605",
        "EnqueuedTime": "2018-12-27T16:02:58.0260000Z",
        "StreamId": null
    }
}
]

This output was obtained from the sample data utility in the Job.

I tried to set up a test query that collects only a single column for the DB (dvcid), however when I test the query I get a message with" An unexpected error has occured."

 SELECT
 StageElement.arrayvalue.dvcid as dvcid
 INTO [Guardardb]
 FROM [Pc-Sdm230] AS evento
 CROSS APPLY GetArrayElements(evento.valores) AS StageElement

The job fails when I try to start it.

When I test the following query everything works fine, and I can see all the array Valores in a single column:

SELECT
*
INTO [Guardardb]
FROM [Pc-Sdm230] AS evento

I cant figure out what I am doing wrong.

Answer 1

GetArrayElement is used for Array elements in JSON. In your case, you have nested JSON, so you need to use GetRecordProperties.

Here's the modified queries

   SELECT 
   StageElement.PropertyValue.dvcid as dvcid
   INTO [Guardardb]
   FROM [Pc-Sdm230] AS evento
   CROSS APPLY GetRecordProperties(evento.valores) AS StageElement

Let me know if it works for you.

Thanks,

JS