How to correctly create new documents with group by

Couchbase Server
1

Hi all,

I am having a hard time in making new arrays in query and really need help.

I have this type of document

{
“countryId”: 602,
“gameId”: 1,
“departmentId”: 549,
“oddsCount”: 556
},
{
“countryId”: 602,
“gameId”: 1,
“departmentId”: 548,
“oddsCount”: 547
}

{
“countryId”: 603,
“gameId”: 1,
“departmentId”: 549,
“oddsCount”: 556
},
etc…

I want to group first by gameId, then by countryId and then by departmentId, and i want a sum of oddsCount by game id.
So i want to make this kind of document.

[
{
“countries”: [
{
“id”: 602,
“departmentId”: [
{
549, 548
}
]
},
{
“id”: 603,
“leagues”: [
{
“id”: 549
}
]
}
],
“gameId”: 1,
“oddsCount”: 9772
},
]

Can this be done? i have made several attempts but no success.

2 
SELECT t2.gameId, ARRAY_AGG({t2.departments, t2.countryId}) AS countries, SUM(t2.s) AS oddsCount
FROM (SELECT t1.gameId , t1.countryId, SUM(t1.s) AS s, ARRAY_AGG(t1.departmentId) AS departments
      FROM (SELECT  gameId , countryId, departmentId, SUM(oddsCount) AS s
            FROM  default
            GROUP BY gameId , countryId, departmentId ) AS t1
      GROUP BY t1.gameId , t1.countryId) AS t2
GROUP BY t2.gameId;
3

Oh man, thanks a lot!