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

Nathaniel Wolf 0 Reputation points
2023-07-25T16:46:20.2966667+00:00

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.

User's image

Azure Event Hubs
Azure Event Hubs
An Azure real-time data ingestion service.
526 questions
Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
323 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
18,591 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Sander van de Velde 26,801 Reputation points MVP
    2023-07-25T20:04:50.16+00:00

    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:

    User's image

    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.