azureazure-stream-analyticsstream-analytics

Pivoting JSON Data Rows into Column

I need Help in Following. I have data like

[{
"id": "0001",
"type": "donut",
"name": "Cake",
"topping":
    [
        { "id": "5003", "type": "Chocolate" },
        { "id": "5004", "type": "Maple" }
    ]
}]

I want to Convert this into following

enter image description here

Parameters will be dynamic or Multiple, not just Chocolate and Maple )

I want to create a stream analytics query who deals with this data and store into the Destination table who have this columns already like Id, Name, Type, Chocolate, Maple...... Please Help me in this.

Solution

  • You could get help from udf in ASA.

    UDF code:

    function main(arg) {
    var array = arg.topping;
    var map = {};
    map["id"] = arg.id;
    map["type"] = arg.type;
    map["name"] = arg.name;
    for(var i=0;i<array.length;i++){        
        var key=array[i].type;        
        map[key] = array[i].id;      
    }
    return map;  
}

    SQL:

    WITH 
c AS
(
    SELECT 
    udf.processArray(jsoninput) as result
    from jsoninput
)

select c.result
INTO
    jaycosmos
from c

    Sample data:

    [{
"id": "0001",
"type": "donut",
"name": "Cake",
"topping":
    [
        { "id": "5003", "type": "Chocolate" },
        { "id": "5004", "type": "Maple" }
    ]
},
{
"id": "0002",
"type": "donut2",
"name": "Cake2",
"topping":
    [
        { "id": "5005", "type": "Chocolate" }
    ]
}
]

    Output:

    enter image description here