CODEWITHSUNDEEP
azureazure-stream-analyticsstream-analytics
Sagar Malandkar
Sagar Malandkar

Reputation: 49

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.

Upvotes: 2

Views: 2601

Answers (2)

Ruskin
Ruskin

Reputation: 6161

Building on Jay's answer, you can do the same thing more performantly using the following udf code:

function main(arg) {
    return arg.map(x => (
        x.topping.reduce((acc, cur) => {
            acc[cur.type] = cur.id; // dynamically add items from topping array
            return acc;
        }, { id: x.id, type: x.type, name: x.name }) // initialise with static items
    )); 
}

Unfortunately as of writing, CosmosDb does not support the ECMAScript2018 and so you cannot use the spread operator ... on objects, otherwise you'd be able to to the following one-liner:

function main(arg) {
    return arg.map(x => (
        x.topping.reduce(
            (acc, cur) => ({ ...acc, [cur.type]: cur.id }), 
            { id: x.id, type: x.type, name: x.name }
        )
));
}

Upvotes: 1

Jay Gong
Jay Gong

Reputation: 23782

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

Upvotes: 1

Related Questions