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

Jean-Sébastien · Accepted Answer · 2017-10-20 18:40:06Z

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, 2017 at 18:40

Jean-Sébastien

7373 silver badges7 bronze badges

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

2 Comments

Ahmed Essam Over a year ago

Dear JS Azure, would you please have a look on this question, your feedback would be highly appreciated :) stackoverflow.com/questions/47141061/…

Jean-Sébastien Over a year ago

@AhmedEssam: sorry I missed your message initially. I saw one of our team member already answer the thread. Hope you are now unblocked.

DidacticTactic · Accepted Answer · 2017-10-25 00:05:46Z

-1

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.

answered Oct 25, 2017 at 0:05

DidacticTactic

943 bronze badges

1 Comment

jbjon Over a year ago

This URL is now used for a career profile product. It appears this HTTP API no longer exists.

Collectives™ on Stack Overflow

Azure Stream Analytics–Querying JSON Arrays of arrays

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related