MongoDB - Count how many users have item inside their array

[Ran Alcobi]

I have a question in MongoDB query. I have the following users collection:

{
    "_id" : ObjectId("5aa03bf97d6e1d28a020f488"),
    "name":"A1",
     "interests" : [ 
        ObjectId("5aa03b877d6e1d28a020f484"), 
        ObjectId("5aa03bb47d6e1d28a020f485")
    ]
},
{
    "_id" : ObjectId("5affd69339f67335303ddf77"),
    "name":"A2",
     "interests" : [ 
        ObjectId("5aa03b877d6e1d28a020f484")
    ]
},
{
    "_id" : ObjectId("5affd69339f673ddfjfhri45"),
    "name":"A3",
     "interests" : [ 
        ObjectId("5aa03bb47d6e1d28a020f485"), 
    ]
},
{
    "_id" : ObjectId("5affd69339f67365656ddfg4f"),
    "name":"A4",
     "interests" : [ 
        ObjectId("5aa16eb8890cbb4c582e8a38"), 
    ]
}

The interests collection look li the following example:

{
    "_id" : ObjectId("5aa16eb8890cbb4c582e8a38"),
    "name" : "Swimming",
},
{
    "_id" : ObjectId("5aa03bb47d6e1d28a020f485"),
    "name" : "Basketball",
},
{
    "_id" : ObjectId("5aa03b877d6e1d28a020f484"),
    "name" : "Fishing",
}

I want to write a query that counts for the interests types of all users: the expected result is like:

 [
    {
        "name":"fishing"
        "count":21 
    },
    {
        "name":"Basketball"
        "count":15
    }
]

 

Thanks for helpers :)

[Kevin Adistambha]

Hi

Using your example documents, you can do what you require using a series of aggregation pipeline stages:

db.test.aggregate([

  // 1. unwind the interests array
  {$unwind: '$interests'},

  // 2. group and count by interest
  {$group: {_id: '$interests', count: {$sum: 1}}},

  // 3. do a lookup using the "interests" collection
  {$lookup: {from: 'interests', localField: '_id', foreignField: '_id', as: 'lookup'}},

  // 4. unwind the lookup's result
  {$unwind: '$lookup'},

  // 5. project the final output
  {$project:{_id:0, name: '$lookup.name', count: '$count'}}
])

In this example, the main collection was called test and the collection containing the interests are called interests.

However, if you don’t really change the interest names that often, it may be better to embed the interests collection inside the main collection instead. For example:

{
  "_id": ObjectId("5b42f142c8be2bc45c3bcbe2"),
  "name": "A1",
  "interests": [
    "Fishing",
    "Basketball"
  ]
}
{
  "_id": ObjectId("5b42f14cc8be2bc45c3bcbe3"),
  "name": "A2",
  "interests": [
    "Fishing"
  ]
}
{
  "_id": ObjectId("5b42f154c8be2bc45c3bcbe4"),
  "name": "A3",
  "interests": [
    "Basketball"
  ]
}
{
  "_id": ObjectId("5b42f15cc8be2bc45c3bcbe5"),
  "name": "A4",
  "interests": [
    "Swimming"
  ]
}

With this design, you don’t need to the aggregation steps 3, 4, and 5. It’s sufficient to do $unwind then $group, and maybe $project to format the output documents nicely.

For more details regarding MongoDB schema design, please see:

• 6 Rules of Thumb for MongoDB Schema Design: Part 1, Part 2, Part 3
• Data Models
• Use Cases

Best regards
Kevin

​