Azure Stream Analytcs Reading data from Event Hub

I have the below structure JSON data coming from event hub, and I am trying to process the same using Azure Stream Analytics. I have tried some of the methods to extract the array elements from that JSON structure, but all the query method has been showing one or the other errors.

If I try flattening the "JSON" using CTE and some functions, the query throws errors like the functions are not supported. Any suggestion available to retrieve the below "JSON" data using Azure stream analytics?

[ { "User": [ { "status": "Active", "userId": "11000", "username": "test01.test02@samplemail.pl", "firstName": "test01", "lastName": "test02", "email": "test02.test01@samplemail.pl", "Manager": "Test04", "HumanResource": "Test 03" } ] } ]

Regards, Sandeep

Since User is reserved keyword, you need to enclose them within brackets[User] in the query. I have made few changes in your query. Below is the modified query.

select 
a.arrayvalue.status,a.arrayvalue.UserId,a.arrayvalue.username,
a.arrayvalue.firstname, a.arrayvalue.lastname, a.arrayvalue.email,
a.arrayvalue.manager, a.arrayvalue.humanresource
from input as i cross apply getArrayElements(i.[User]) a

This code uses the GetArrayElements function to extract the User array from the input JSON, and then uses CROSS APPLY to flatten the array into individual rows. Finally, it selects the individual fields from the flattened data.