DB Data -

[{
    title: "Vivo X50",
    category: "mobile",
    amount: 35000

},
{
    title: "Samsung M32",
    category: "mobile",
    amount: 18000

},
{
    title: "Lenovo 15E253",
    category: "laptop",
    amount: 85000

},
{
    title: "Dell XPS 15R",
    category: "laptop",
    amount: 115000
}]

Expected Output:

[{
    category: "mobile",
    qty: 2,
    totalAmount: 53000
},
{
    category: "laptop",
    qty: 2,
    totalAmount: 200000
}]

Code I am running (Using mongoose)

let products = await Product.aggregate([
    {
      $project: { _id: 0, category: 1, amount: 1 },
    },
    {
      $group: {
        _id: "$category",
        qty: { $sum: 1 },
        totalAmount: { $sum: "$amount" },
      },
    },
  ]);

Result I am Getting.

[
{
"_id": "laptop",
"count": 2,
"totalSum": 200000
},
{
"_id": "mobile",
"count": 2,
"totalSum": 53000
}
]

As you can clearly see that I am able to get correct data but I want correct name also category instead of _id. Please help me with that. Thanks in advance

CodePudding user response：

You need $project as the last stage to decorate the output document.

{
  $project: {
    _id: 0,
    category: "$_id",
    qty: "$qty",
    totalAmount: "$totalAmount"
  }
}

Meanwhile, the first stage $project doesn't need.

db.collection.aggregate([
  {
    $group: {
      _id: "$category",
      qty: {
        $sum: 1
      },
      totalAmount: {
        $sum: "$amount"
      }
    }
  },
  {
    $project: {
      _id: 0,
      category: "$_id",
      qty: "$qty",
      totalAmount: "$totalAmount"
    }
  }
])

Sample Mongo Playground

CodePudding user response：

You can use the following query to get your expected output. cheers~

await Product.aggregate([
  {
    $group: {
      _id: "$category",
      qty: {
        $sum: 1
      },
      totalAmount: {
        $sum: "$amount"
      },
      
    },
    
  },
  {
    $addFields: {
      category: "$_id"
    }
  },
  {
    $project: {
      _id: 0
    },
    
  }
])