mongodb - Efficient way to get the total amount of products sold by each category

Question

I have a problem as follows. I need to get the total amount of products sold for each category. A product can fall into more than one category. A category can also include multiple products in a single category. I have the following scheme:

//Categories scheme
{
  name: {
        type: String,
        required: [true, "The Category name is required"],
        unique: true,
    }}

 //Products
{
  name: {
        type: String,
        required: [true, "The prodcut name is required"],
        unique: true,
    }
    price: {
        type: mongoose.Types.Decimal128,
        default: "0.0",
    },
 }

 //Products Categories scheme
{ 
    product: {
        type: Schema.Types.ObjectId, ref: "Products",
    },
    category: {
        type: Schema.Types.ObjectId, ref: "Categories",
    },
 }
 //Orders model scheme
{ 
    product: {
        type: Schema.Types.ObjectId, ref: "Products",
    },
    price: {
        type: mongoose.Types.Decimal128,
        default: "0.0",
    },
    quantity: {
        type: Number,
        default: 0,
    }
 }

I have schemes like the one above. I tried to create a query as follows but it only takes one product.

OrdersModel.aggregate(
        [
            { $lookup: { from: 'products', localField: 'product', foreignField: '_id', as: 'p' } },
            { $lookup: { from: 'prodcutcategories', localField: 'p._id', foreignField: 'product', as: 'pc' } },
            { $lookup: { from: 'categories', localField: 'pc.category', foreignField: '_id', as: 'cat' } },
            {
                $group:
                {
                    _id: "$cat.name", 
                }
            },
        ]
    )

This query worked but produced one product, as follows:

{
"status": "Success",
"data": [
    {
        "_id": [
            "A category",
            "A product"
        ]
    }
 ]
}

But i need like that:

{
"status": "Success",
"data": [
    {
        "category": "Category name 1",
        "totalPrice": 10000,
        "qty": 10000,
        "mostSoldProduct": "Product name",
        ...
    },
    {
        "category": "Category name 2",
        "totalPrice": 10000,
        "qty": 10000,
        "mostSoldProduct": "Product name",
        ...
    }
 ]
}

I should get the same result as above. But I say am I wrong in making the query. I created a query similar to the following in MSSQL.

select cat.name sum(o.price) from order o 
inner join products p on p.id=o.productid
inner join products_categories pc on p.id=pc.productid
inner join catrgories cat on cat.id=pc.category_id
group by cat.name

What are the ways to solve my problem? I searched a lot on Google but I can't find a solution on MongoDB. Thanks for the answers!

Answer 1

Add below stages after your pipeline stages,

• $unwind deconstruct cat array to make an object
• $unwind deconstruct p array to make and object
• $group by category name and count required statistics

  { $unwind: "$cat" },
  { $unwind: "$p" },
  {
    $group: {
      _id: "$cat.name",
      totalPrice: { $sum: "$price" },
      qty: { $sum: "$quantity" },
      totalSold: { $sum: 1 }
    }
  }

Playground

You really need to change your schema to modern schema, this structure is not good for mongo database.