I have this type of dataset in my MongoDB database :

{
"name": "Alex",
"domains": {
      "domain_1": 1,
      "domain_2": 1,
      "domain_3": 0
    }
},
{
"name": "Arthur",
"domains": {
      "domain_1": 1,
      "domain_2": 0,
      "domain_3": 3
    }
}
{
"name": "Paul",
"domains": {
      "domain_1": 0,
      "domain_2": 2,
      "domain_3": 3
    }
}

How can I get an output like this ? (group by domain's name and counting the number of level associated. I'm looking for a mongoDB query matching this output of something close:

{
    "domain_1": {
        "level_0": 1, #For my domain_1, I have 1 user with the level 0;
        "level_1": 2, #For my domain_1, I have 2 user with the level 1;
        "level_2": 0, #For my domain_1, I have 0 user with the level 2;
        "level_3": 0, #For my domain_1, I have 0 user with the level 3;
    },
    "domain_2": {
        "level_0": 1, # #For my domain_2, I have 1 user with the level 0;
        "level_1": 1, # ...
        "level_2": 1,
        "level_3": 0,
    },
    "domain_3": {
        "level_0": 1,
        "level_1": 0,
        "level_2": 0,
        "level_3": 2,
    }
}

Thanks for your help

CodePudding user response：

One option is:

1. Use $objectToArray and $unwind to get simple documents
2. $group by domain and level to count
3. $group only by domain to create the wanted object format
4. $mergeObjects with the format to fill emptyentries
5. $group to format as one object according the requested result

db.collection.aggregate([
  {$project: {_id: 0, data: {$objectToArray: "$domains"}}},
  {$unwind: "$data"},
  {$group: {_id: {domain: "$data.k", level: "$data.v"}, count: {$sum: 1}}},
  {$group: { 
      _id: "$_id.domain", 
      data: {$push: {k: {$concat: ["level_", {$toString: "$_id.level"}]}, v: "$count"}}
  }},
  {$project: {
      k: "$_id",
      v: {$mergeObjects: [
          {
            "level_0": 0,
            "level_1": 0,
            "level_2": 0,
            "level_3": 0
          },
          {$arrayToObject: "$data"}
        ]
      }
  }},
  {$group: {_id: 0, data: {$push: {k: "$k", v: "$v"}}}},
  {$replaceRoot: {newRoot: {$arrayToObject: "$data"}}}
])

See how it works on the playground example