Count item occurrences in an array with mongodb aggregation framework

Question

I am working in a project with express and mongodb (I am using mongoose) and I have a collection whith the following structure:

[
{
    "_id": "5e4a39f01ab49e48d4db5b9c",
    "last": {
        "winningNumbers": {
            "list": [
                66,
                71,
                62,
                50,
                29,
                67,
                74,
                43,
                22,
                1,
                11,
                3,
                80,
                17,
                57,
                53,
                30,
                19,
                76,
                40
            ],
            "bonus": [
                40
            ]
        }
    }
},
{
    "_id": "5e4a3b1c1ab49e48d4db5cb8",
    "last": {
        "winningNumbers": {
            "list": [
                71,
                46,
                79,
                60,
                1,
                14,
                19,
                73,
                34,
                55,
                5,
                65,
                12,
                47,
                16,
                62,
                51,
                53,
                3,
                45
            ],
            "bonus": [
                45
            ]
        }
    }
}

]

I have tried to use the mongodb aggregation framework to count the occurrences of each number in the last.winningNumber.list array in the whole collection but I did not achieved that.

I want to have as ouput something like below:

{
    "occurrences": [
        {number:1,count:3},
        {number:2,count:8},
        {number:12,count:15},
        ...
        {number:78,count:55},
    ]
}

Is there any way to achieve that using $group or should I use something different?

Thank you!

score 2 · Accepted Answer · answered Feb 23 '20 at 23:04

$group is the way to achieve this, but you have to $unwind your array before.

db.collection.aggregate([
  {
    $project: {
      "last.winningNumbers.list": 1
    }
  },
  {
    $unwind: "$last.winningNumbers.list"
  },
  {
    $group: {
      _id: "$last.winningNumbers.list",
      count: {
        $sum: 1
      }
    }
  }
])

Note : the first $project stage is not mandatory, and it will produce exactly the same result without it. However, I higly recommand to keep it on place, as unwinding / grouping can be expensive operations. (it will produce one document per array element).

Thank you! I have already dane it with the same way. That helps me a lot! — atheodos, Feb 24 '20 at 11:33

Count item occurrences in an array with mongodb aggregation framework

1 Answers1