MongoDB aggregate $lookup not working with $group-CodePudding

I have two collections in my db: categories & products and I want to list all categories and number of products they all have. Inside of products schema I have 'foreign key' that is pointing to _id column in categories collection so I formed a query like this :

db.categories.aggregate(
  {
    $lookup:{
        from:"products",
        localField:"_id",
        foreignField:"product_category",
        as:"category_products"
     }
   }
)

and I receive this as a result:

{ _id: ObjectId("613cde06e806c050fcf073eb"),
  name: 'Drinks',
  category_products: 
   [{ _id: ObjectId("61476348e4dfdb0d54d38a29"),
       product_name: 'Alpro Almond Miln',
       product_price: 1.99,
       product_category: ObjectId("613cde06e806c050fcf073eb"),
       createdAt: 2021-09-19T16:20:24.269Z,
       updatedAt: 2021-09-19T16:20:24.269Z,
       __v: 0 },
     { _id: ObjectId("6147a974e4dfdb0d54d38aee"),
       product_name: 'Coca Cola 2l',
       product_price: 1.99,
       product_category: ObjectId("613cde06e806c050fcf073eb"),
       createdAt: 2021-09-19T21:19:48.105Z,
       updatedAt: 2021-09-19T21:19:48.105Z,
       __v: 0 }]}

and now I want to calculate number of products that this category called 'Drinks' has. I form a query like this :

  db.categories.aggregate(
  {
    $lookup:{
        from:"products",
        localField:"_id",
        foreignField:"product_category",
        as:"category_products"
     }
   },   
    {
     $group : {
         _id : '$name',
         num_of_products : {
          $sum : "$category_products._id" 
        } 
      }
    }
)

And as a result I get :

{ _id: 'Drinks', num_of_products: 0 }

So my question is how to properly calculate number of products per each category in collection. I expect to get a result like this :

{ _id: 'Sweets', num_of_products: 2 }
{ _id: 'Snacks', num_of_products: 5 }
{ _id: 'Drinks', num_of_products: 1 }
{ _id: 'Coffee', num_of_products: 6 }

CodePudding user response：

Looks like you dont need $group, you have the category products in an array so taking its $size looks enough.

Try to add $project this as next stage.

*i am not sure if you want this because in your sample data there are no sweets,snacks,coffee only drinks, and you have 2 drinks, and showing that you expect only 1 drink

 {
  "$project" : {
    "_id" : 0,
    "name" : "$name",
    "num_of_products" : {
      "$size" : "$category_products"
    }
  }
}