Azure Stream Analytics query of nested array/record

Question

I have the following example of JSON which I am trying to send to PowerBI through Azure Stream Analytics.

[{

      "timestamp":1526452793090,
      "values":[
         {
            "id":"Device1.K1001",
            "v":false,
            "q":true,
            "t":1359326344047
         }
      ]
}]

My first assumption was to do the following query:

SELECT 
    timestamp,
    [values].id,
    [values].v,
    [values].q,
    [values].t
INTO
    [PowerBI]
FROM
    [IoTHub]

result in powerBI

Why is the above solution wrong?

Best regards Michael

It is wrong because values is an array, and you are accessing it like it is a complex type. — Peter Bons
– Peter Bons, Commented May 16, 2018 at 8:11
@Michael You're welcome ,please mark this answer for others' reference,thx — Jay Gong
– Jay Gong, Commented May 24, 2018 at 5:56
@Michael You're welcome ,please mark this answer for others' reference,thx — Jay Gong
– Jay Gong, Commented Aug 30, 2018 at 5:57

Jay Gong · Accepted Answer · 2018-05-16 08:47:44Z

3

You could get GetArrayElement ,please refer to my sample query:

WITH 
temp AS
(
SELECT
timestamp ,
GetArrayElement([values],0)as valueObj 
FROM jsoninput 
)

SELECT
    temp.timestamp ,temp.valueObj.id,temp.valueObj.v,temp.valueObj.q,temp.valueObj.t
INTO
    jaycosmostest
FROM
    temp

Output Result:

Hope it helps you.

answered May 16, 2018 at 8:47

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Over a year ago

Thank you very much. That is the solution. I failed to understand the array. Best regards Michael.

Jay Gong Over a year ago

@Michael You're welcome ,please mark this answer for others' reference,thx

Jay Gong Over a year ago

@Michael You're welcome ,please mark this answer for others' reference,thx

Collectives™ on Stack Overflow

Azure Stream Analytics query of nested array/record

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related