none
Get Array Elements By Name RRS feed

  • Question

  • 

    I was wondering if it is possible to get the elements of a json array by the name of the property than the position. Consider this data:

        {
        "salesdata": {
            "productsbyzone": {
                "zones": [{
                        "eastzone": "shirts, trousers"
                    },
                    {
                        "westzone": "slacks"
                    },
                    {
                        "northzone": "gowns"
                    },
                    {
                        "southzone": "maxis"
                    }
                ]
            }
        }
    }

    I intend to get the values for each zone by the name of the zone rather than the position. I was able to successfully run the following stream analytics query until I realized that the position of the zones differs in each json that I receive.

    WITH 
    salesData AS
    (
        SELECT
        (c.salesdata.productsbyzone.zone,0) as eastzone,
        (c.salesdata.productsbyzone.zone,1) as westzone,
        (c.salesdata.productsbyzone.zone,2) as northzone,
        (c.salesdata.productsbyzone.zone,3) as sourthzone,
        FROM [sales-data] as c
    )
    SELECT
    eastzone.eastzone as PRODUCTS_EAST,
    westzone.westzone as PRODUCTS_WEST,
    northzone.northzone as PRODUCTS_NORTH,
    southzone.southzone as PRODUCTS_SOUTH
    INTO PRODUCTSDATABASE
    FROM salesData 

    Need a way to retrieve the values by the name rather than the position of the array element. Thanks in advance.

    Sunday, February 11, 2018 7:22 AM

All replies

  • The best solution would be to write custom JavaScript function. You can see similar example in this blog entry: https://blogs.msdn.microsoft.com/streamanalytics/2017/04/04/using-azure-stream-analytics-javascript-udf-to-lookup-values-in-json-array/
    Monday, February 12, 2018 8:10 PM
    Moderator
  • Tis will help 

    https://msdn.microsoft.com/en-us/azure/stream-analytics/reference/complex-data-types-stream-analytics

    Thursday, May 17, 2018 2:51 PM