Azure Stream Analytics–Querying JSON Arrays of arrays

Question

I have a problem writing a query to extract a table out of the arrays from a json file: The problem is how to get the information of the array “data packets” and its contents of arrays and then make them all in a normal sql table.

One hard issue there is the "CrashNotification" and "CrashMaxModuleAccelerations", I dont know how to define and use them.

The file looks like this:

    { "imei": { "imei": "351631044527130F", "imeiNotEncoded":                         
"351631044527130" 
}, 
"dataPackets": [ [ "CrashNotification", { "version": 1, "id": 28 } ], [ 
"CrashMaxModuleAccelerations", { "version": 1, "module": [ -1243, -626, 
14048 ] } ] ]}

I tried to use Get array elements method and other ways but I am never able to access 2nd level arrays like elements of "CrashNotification" of the "dataPackets" or elements of "module" of the array "CrashMaxModuleAccelerations" of the "dataPackets".

I looked also here (Select the first element in a JSON array in Microsoft stream analytics query) and it doesnt work. I would appreciate any help :)

score 2 · Accepted Answer · answered Oct 20 '17 at 18:40

2

Based on your schema, here's an example of query that will extract a table with the following columns: emei, crashNotification_version, crashNotification_id

WITH Datapackets AS
(
SELECT imei.imei as imei,
        GetArrayElement(Datapackets, 0) as CrashNotification
FROM input
)
SELECT  
    imei,
    GetRecordPropertyValue (GetArrayElement(CrashNotification, 1), 'version') as crashNotification_version,
    GetRecordPropertyValue (GetArrayElement(CrashNotification, 1), 'id') as crashNotification_id
FROM Datapackets

Let me know if you have any further question.

Thanks,

JS (Azure Stream Analytics)

answered Oct 20 '17 at 18:40

Jean-Sébastien

737
3
7

Dear JS Azure, would you please have a look on this question, your feedback would be highly appreciated :) https://stackoverflow.com/questions/47141061/azure-stream-analytics-error-with-customized-timestamp-by-while-applying-win – Ahmed Essam Nov 08 '17 at 14:16
@AhmedEssam: sorry I missed your message initially. I saw one of our team member already answer the thread. Hope you are now unblocked. – Jean-Sébastien Nov 22 '17 at 00:28

score -1 · Answer 2 · answered Oct 25 '17 at 00:05

We built a HTTP API called Stride for converting streaming JSON data into realtime, incrementally updated tables using only SQL.

All you'd need to do is write raw JSON data to the Stride API's /collect endpoint, define continuous SQL queries via the /process endpoint, and then push or pull data via the /analyze endpoint.

This approach eliminates the need to deal with any underlying data infrastructure and gives you a SQL-based approach to this type of streaming analytics problem.

This URL is now used for a career profile product. It appears this HTTP API no longer exists. — jbjon, Mar 30 '21 at 11:28

Azure Stream Analytics–Querying JSON Arrays of arrays

2 Answers2