Azure stream analytics custom output data with CASE statement

Question

I have problem to counting user of each zone, each user will send locale of them to server. Can someone help me to achieve this, thank you. Output stream analytics should be this:

{
   "sessionId": "sessionId1", -->group by sessionId
   "data": {
        "North America": 0,
        "South America": 0,
        "Europe": 0,
        "Russia": 1,
        "Africa": 2,
        "India": 0,
        "China": 2,
        "Oceania": 0, 
   }
},
{
   "sessionId": "sessionId2", -->group by sessionId
   "data": {
        "North America": 0,
        "South America": 0,
        "Europe": 0,
        "Russia": 0,
        "Africa": 1,
        "India": 0,
        "China": 0,
        "Oceania": 0, 
   }
}

input data:

[{"sessionId":"Session1","deviceId":"deviceTest1","locale":"Russia"},{"sessionId":"Session1","deviceId":"deviceTest2","locale":"China"},{"sessionId":"Session1","deviceId":"deviceTest3","locale":"China"},{"sessionId":"Session1","deviceId":"deviceTest4","locale":"Africa"},{"sessionId":"Session1","deviceId":"deviceTest5","locale":"Africa"},{"sessionId":"Session2","deviceId":"deviceTest6","locale":"Africa"}]

Answer 1

I have tried serval SQL query, but cant achieve your requirement. I think it is impossible because you need group by sessionId, but there is no Aggregate Functions can get this format.

"data": {
        "North America": 0,
        "South America": 0,
        "Europe": 0,
        "Russia": 0,
        "Africa": 1,
        "India": 0,
        "China": 0,
        "Oceania": 0, 
   }