Pivoting JSON Data Rows into Column

Question

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

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.

score 1 · Accepted Answer · edited Jul 07 '19 at 09:20

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:

score 1 · Answer 2 · answered Apr 14 '21 at 07:01

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 }
        )
));
}

Pivoting JSON Data Rows into Column

2 Answers2