locked
Extracting array JSON data from IoT Hub messages RRS feed

  • Question

  • Currently I have data flowing from an IoT Hub to my Stream Analytics Job. Due to a gateway connection we have set up there are a number of events being bundled together and sent in the form:

    {"timestamp":1515528002233,"values":
    
    [{"id":"ExampleTagID","v":0,"q":1,"t":1515524982940},
    {"id":"ExampleTagID","v":0,"q":1,"t":1511440551088},
    {"id":"ExampleTagID","v":0,"q":1,"t":1511440551088},
    {"id":"ExampleTagID","v":0,"q":1,"t":1511440551088},
    ...
    {"id":"ExampleTagID","v":11,"q":1,"t":1515527982168},
    {"id":"ExampleTagID","v":"3","q":0,"t":1515524596570},
    {"id":"ExampleTagID","v":"2","q":1,"t":1515522266542}],
    
    "EventProcessedUtcTime":"2018-01-09T20:00:02.5312423Z",
    "PartitionId":2,
    "EventEnqueuedUtcTime":"2018-01-09T20:00:02.1900000Z",
    
    "IoTHub":{
    "MessageId":null,
    "CorrelationId":null,
    "ConnectionDeviceId":"example",
    "ConnectionDeviceGenerationId":"1234567890",
    "EnqueuedTime":"2018-01-09T20:00:02.1590000Z",
    "StreamId":null}}

    It seems because the relevant event information is embedded in the array, ASA will just store the data as it comes, an array.

    Is there a way to point ASA towards the values array and perform its regular processing (since the data is in the proper format for this) or a particular query I can perform to extract all of the objects?

    Something like:

    SELECT
        timestamp as Processed_Time,
        val.ArrayValue.id as ID,
        vals.ArrayValue.v as Value,
        vals.ArrayValue.t as Gathered_Time,
        vals.ArrayValue.q as Tag_Quality
    INTO [Output-IoTHub-One] FROM [Input-IoTHub-One] as e
    
    CROSS APPLY GetElements(e.values) as vals

    Thanks,
    CSparling

    Wednesday, January 10, 2018 3:05 PM

Answers

  • Hi,

    I made some small edits in your query to return the individual values, not the array.

    In particular I added brackets around "values" since it is a reserved keyword.

    SELECT
        System.timestamp as Processed_Time,
        vals.ArrayValue.id as ID,
        vals.ArrayValue.v as Value,
        vals.ArrayValue.t as Gathered_Time,
        vals.ArrayValue.q as Tag_Quality
    INTO output FROM iothub as e
    CROSS APPLY GetElements(e.[values]) as vals

    Is it what you wanted to do? If not let us know.

    Thanks,

    JS

    • Marked as answer by CSparling Monday, January 22, 2018 9:16 PM
    Wednesday, January 10, 2018 8:17 PM