mongodb - mongo count rows from an array of provided data

Question

I have collection like this:

{ 
   "_id" : ObjectId("4d663451d1e7242c4b68e000"), 
  "topic" : "abc", 
  "subLevel" : {
     "id" : 1
  }
}
{ 
    "_id" : ObjectId("4d6634514cb5cb2c4b69e000"), 
    "topic" : "bce", 
    "subLevel" : {
        "id" : 1
     }
}

{ 
    "_id" : ObjectId("4d6634514cb5cb2c4b70e000"), 
    "topic" : "bec", 
    "subLevel" : {
        "id" : 2
     }
}
{ 
    "_id" : ObjectId("4d6634514cb5cb2c4b70e000"), 
    "topic" : "vvv", 
    "subLevel" : {
        "id" : 3
     }
}

and I need to count how many documents exist for provided subLevel.id list, for example if I provide 1 and 2 it should show me that for 1 we have 2 documents and for 2 only 1 document and simply omit document where subLevel.id is 3 as it's not in the list of id's.

I tried to do it with a aggregate

db.getCollection('products').aggregate( [
   { $project: 
       {  "has_sublevel" : {$in: [ "subLevel.id", [1 , 2 ]]} }
   },
   { $group: { _id : "$subLevel.id", count: { $sum: 1 } } }
] )

but result is

{
   _id : null,
   count: 4
}

how can I do it, thanks in advance!

If transform it to SQL which I familiar more, query should look like this:

select subLevelId, count(id) FROM products where subLevelId in (1,2) group by subLevelId

Answer 1

If I've understand correctly, you are so so close, check this query:

• First use $match to get only documents whose subLevel.id is 1 or 2.
• Then, as you have done, $group by the id and sum to get total count:

db.collection.aggregate([
  {
    "$match": { "subLevel.id": { "$in": [ 1, 2 ] } }
  },
  {
    "$group": { "_id": "$subLevel.id", "count": { "$sum": 1 } }
  }
])

Example here