CODEWITHSUNDEEP
arraysazure-stream-analytics
Ahmed Essam
Ahmed Essam

Reputation: 79

Azure Stream Analytics–Querying JSON Arrays of arrays

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 :)

Upvotes: 0

Views: 2942

Answers (2)

DidacticTactic
DidacticTactic

Reputation: 94

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.

Upvotes: -1

Jean-Sébastien
Jean-Sébastien

Reputation: 737

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)

Upvotes: 2

Related Questions