sql - Azure 流分析 : Get Array Elements by name

Question

我想知道我是否可以通过属性名称而不是位置来获取数组的元素。例如，这是我传入的数据:

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

我打算将其移至 SQL 数据库，并且数据库中的每个区域都有列。问题是每个 json 中不同区域的顺序会发生变化。我成功地使用了以下查询，直到我意识到每个 json 中区域的位置发生了变化:

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 

需要一种通过名称而不是位置来引用这些字段的方法。

Answer 1

我推荐一个解决方案:使用 JavaScript UDF在 azure 流作业中完成列排序。

请引用我的示例:

输入数据(打乱顺序):

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

js udf 代码:

function test(arg) {
    var z = arg;
    var obj = {
        eastzone: "",
        westzone: "",
        northzone: "",
        southzone: ""
    }   
    for(var i=0;i<z.length;i++){
        switch(Object.keys(z[i])[0]){
            case "eastzone": 
                obj.eastzone = z[i]["eastzone"];
                continue;
            case "westzone": 

                obj.westzone = z[i]["westzone"];
                continue;
            case "northzone": 

                obj.northzone = z[i]["northzone"];
                continue;
            case "southzone": 
                obj.southzone = z[i]["southzone"];
                continue;
        }
    }
    return obj;
}

您可以在obj参数中定义您想要的顺序

SQL:

WITH 
c AS
(
    SELECT 
    udf.test(jsoninput.salesdata.productsbyzone.zones) as result
    from jsoninput
),
b AS
(
  SELECT 
    c.result.eastzone as east,c.result.westzone as west,c.result.northzone as north,c.result.southzone as south
    from c
)

SELECT
    b.east,b.west,b.north,b.south
INTO
    jaycosmos
FROM
    b

输出:

希望对您有帮助。