Azure Event Hub receiving JSON with extraneous outer array bracket into Stream Analytics

Question

As you can see below my events are being received with an outer array bracket, however they only ever contain 1 actual record (this is desired). I understand that this is probably how the source data comes in, and if were receiving as an actual JSON object I should be able to access my desired fields with the dot operator (I only 4, some which are deeply nested). When I used the no code editor and expand, it's able to properly deduce the nested elements however I need to use functions which only work in the query editor. Any advice on how to tackle this would be appreciated.

Answer 1

Hello @Nathaniel Wolf ,

welcome to this moderated community forum.

Although we cannot see a complete example of the incoming JSON message, the 'records' field is indeed a JSON array with just one element.

I expect the ingested data will look like:

{
    "records" : [
        {
            "time" : "2017-01-01T00:00:00.000Z",
            "resourceId" : "resource1", 
            "operationName" : "operation1",
            "operationVersion" : 1.0
        },
        {
            "time" : "2017-01-02T00:00:00.000Z",
            "resourceId" : "resource2", 
            "operationName" : "operation3",
            "operationVersion" : 2.0
        },
        {
            "time" : "2017-01-03T00:00:00.000Z",
            "resourceId" : "resource2", 
            "operationName" : "operation3",
            "operationVersion" : 3.0
        }
    ]
}

To read all fields, use the GetArrayElement :

SELECT
    GetArrayElement(records, 0).time as time1,
    GetArrayElement(records, 0).resourceId as resourceId1,
    GetArrayElement(records, 0).operationName as operationName1,
    GetArrayElement(records, 0).operationVersion as operationVersion1,
    GetArrayElement(records, 1).time as time2,
    GetArrayElement(records, 1).resourceId as resourceId2,
    GetArrayElement(records, 1).operationName as operationName2,
    GetArrayElement(records, 1).operationVersion as operationVersion2,
    GetArrayElement(records, 2).time as time3,
    GetArrayElement(records, 2).resourceId as resourceId3,
    GetArrayElement(records, 2).operationName as operationName3,
    GetArrayElement(records, 2).operationVersion as operationVersion3
INTO
    EventHub1
FROM
    Input11

This results in the separate fields:

You see that all value are now set in one output row.

If you want to turn each array element in a separate row, check out the cross apply.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.