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!

matthPen · Accepted Answer · 2020-02-23 23:04:45Z

3

$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).

answered Feb 23, 2020 at 23:04

matthPen

4,3731 gold badge18 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

atheodos Over a year ago

Thank you! I have already dane it with the same way. That helps me a lot!

Collectives™ on Stack Overflow

Count item occurrences in an array with mongodb aggregation framework

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related