Query for Power BI Output from Azure Stream Analytics with JSON data

Question

I'm having an issue extracting data from IOT Hub to Azure Stream Analytics to Power BI.

Here is the data coming from Stream Analytics:

{  
   "header":{  
      "version":1
   },
   "data":{  
      "treatmentId":"1",
      "machineData":[  
         {  
            "recordId":3,
            "records":[  
               {  
                  "fields":[  
                     {  
                        "value":"+182",
                        "key":"VP"
                     }
                  ],
                  "group":"PR"
               }
            ]
         }
      ]
   },
   "EventProcessedUtcTime":"2018-12-05T16:52:43.6450807Z",
   "PartitionId":0,
   "EventEnqueuedUtcTime":"2018-12-05T16:38:47.1900000Z",
   "IoTHub":{  
      "CorrelationId":null

   }
}

Using the following query:

SELECT * 
INTO DataPowerBI
FROM iothub;

I am getting the following output in PowerBI:

enter image description here

I am not able to get the child level data under "data", like treatment id, machine data, groups, keys. Can I get a query for pushing all levels of the data, both parent and children?

Thanks in advance! Raj

jbazelmans · Accepted Answer · 2019-03-26 08:45:49Z

1

By using select *, you only get the upper level data-fields back. If you want the data that is nested, you need to specify the data you want.

select data.treatmentid will get you the treatmentId

I am not sure how it works with nesting within nesting. You could try select data.machinedata.recordId to get the recordId.

answered Mar 26, 2019 at 8:45

jbazelmans

3031 gold badge6 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Query for Power BI Output from Azure Stream Analytics with JSON data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related