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 jbazelmans · Accepted Answer · 2019-03-26T08:45:49

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.

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

1 Answers