I'm struggling with a mongodb query to merge objects with their values and have the number of visits for each room,

this is an example of the stored data:

[
{
    "roomVisits": {}
},
{
    "roomVisits": {}
},
{
    "roomVisits": {}
},
{
    "roomVisits": {}
},
{
    "roomVisits": {}
},
{
    "roomVisits": {
        "ROOM13": 3
    }
},
{
    "roomVisits": {}
},
{
    "roomVisits": {}
},
{
    "roomVisits": {
        "ROOM4": 5
    }
},
{
    "roomVisits": {
        "ROOM4": 13
    }
},
{
    "roomVisits": {
        "ROOM4": 3,
        "ROOM13": 1
    }
},
{
    "roomVisits": {
        "ROOM4": 4
    }
}

]

What I expect to get:

{
"result": {
    "4": 1,
    "25": 1
}

}

4 in this case is the sum of the visits in the same room, and the 1 is the number of rooms having 4 visits.

Example 2:

[
{

    "roomVisits": {
        "ROOM1": 1
    }
},
{

    "roomVisits": {
        "ROOM1": 8,
        "ROOM2": 1
    }
},
{

    "roomVisits": {
        "ROOM1": 1
    }
},
{

    "roomVisits": {
        "ROOM3": 3
    }
},
{

    "roomVisits": {}
},
{

    "roomVisits": {
        "ROOM1": 1
    }
},
{

    "roomVisits": {}
},
{

    "roomVisits": {
        "ROOM4": 4
    }
},
{

    "roomVisits": {
        "ROOM5": 1,
        "ROOM3": 1
    }
},
{

    "roomVisits": {}
},
{

    "roomVisits": {
        "ROOM1": 4
    }
},
{

    "roomVisits": {}
},
{

    "roomVisits": {
        "ROOM3": 2
    }
},
{

    "roomVisits": {
        "ROOM6": 3,
        "ROOM3": 3
    }
},
{

    "roomVisits": {
        "ROOM3": 6
    }
},
{

    "roomVisits": {}
},
{

    "roomVisits": {
        "ROOM1": 2,
        "ROOM7": 9
    }
},
{

    "roomVisits": {
        "ROOM7": 24,
        "ROOM3": 2
    }
},
{

    "roomVisits": {
        "ROOM3": 4,
        "ROOM7": 1
    }
},
{

    "roomVisits": {}
},
{

    "roomVisits": {
        "ROOM7": 2
    }
},
{

    "roomVisits": {
        "ROOM3": 3
    }
},
{

    "roomVisits": {
        "ROOM6": 5,
        "ROOM3": 20,
        "ROOM1": 2
    }
},
{

    "roomVisits": {
        "ROOM3": 3
    }
},
{

    "roomVisits": {}
},
{

    "roomVisits": {
        "ROOM8": 3,
        "ROOM9": 3,
        "ROOM10": 2,
        "ROOM11": 1,
        "ROOM12": 1,
        "ROOM13": 1
    }
},
{

    "roomVisits": {
        "ROOM10": 3,
        "ROOM14": 2,
        "ROOM9": 2,
        "ROOM13": 8,
        "ROOM11": 2,
        "ROOM15": 1,
        "ROOM8": 3,
        "ROOM12": 5,
        "ROOM16": 1
    }
},
{

    "roomVisits": {
        "ROOM13": 3,
        "ROOM12": 2,
        "ROOM8": 1,
        "ROOM10": 1
    }
},
{

    "roomVisits": {
        "ROOM1": 2
    }
},
{

    "roomVisits": {
        "ROOM3": 4,
        "ROOM17": 3
    }
},
{

    "roomVisits": {
        "ROOM17": 13
    }
}

]

Result:

{
"result": {
    "1": 4,
    "2": 1,
    "3": 1,
    "4": 1,
    "5": 1,
    "6": 1,
    "7": 1,
    "8": 2,
    "12": 1,
    "16": 1,
    "21": 1,
    "36": 1,
    "51": 1
}

}

Thank you in advance.

CodePudding user response：

This query should do the trick. I've created it in this mongo playground

db.collection.aggregate([
  {
    "$match": {
      "roomVisits": {
        "$ne": {}
      }
    }
  },
  {
    "$addFields": {
      "roomVisitsArray": {
        "$objectToArray": "$roomVisits"
      }
    }
  },
  {
    "$unwind": "$roomVisitsArray"
  },
  {
    "$replaceRoot": {
      "newRoot": "$roomVisitsArray"
    }
  },
  {
    "$group": {
      "_id": "$k",
      "visitCount": {
        "$sum": "$v"
      }
    }
  },
  {
    "$group": {
      "_id": "$visitCount",
      "visitCount": {
        "$sum": 1
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "value": {
        "$push": {
          "k": {
            "$toString": "$_id"
          },
          "v": "$visitCount"
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$arrayToObject": "$value"
      }
    }
  }
])