How can I get the most used category in a product in MongoDB collection-CodePudding

I'm still learning MongoDB and I'm having a difficulty on my current problem. How can I get the most used category in collection, so in this JSON, my most used category is CIES. so what I want to do is it to the front end.

[
    {
        "_id": "63888d85674a07e0d7d2eccc",,
        "products": [
            {
                "productId": {
                    "_id": "63888c6c674a07e0d7d2e6ee",
                    "category": "CEIS",
                    "productCategory": "Others"
                },
            }
        ],

    },
    {
        "_id": "63888d92674a07e0d7d2ecf5",
        "products": [
            {
                "productId": {
                    "_id": "63888c17674a07e0d7d2e68a",
                    "category": "CEIS",
                    "productCategory": "CEIS Merchandise"
                },
               
            }
        ],
    },
    {
        "_id": "63888db6674a07e0d7d2ed93",
        "products": [
            {
                "productId": {
                    "_id": "63888c8c674a07e0d7d2e725",
                    "category": "CAHS",
                    "productCategory": "Clinical Equipments"
                },
            }
        ],

    }
]

How do i get the most use category

[
    {
        "category": "CEIS",
        "total" :  2
    }
]

CodePudding user response：

You need to add a sort stage to your aggregation pipeline to sort the categories by the total in descending order, and then only return the first element from the sorted list.

Here is an example using Node

router.get('/top', async (req, res) => {
    
    try {
      const order = await Order.aggregate([
        {
          $group: {
            _id: "$products.productId.category",
            total: {$sum: 1}
          }
        },
        {
          $sort: {
            total: -1
          }
        },
        {
          $limit: 1
        }
      ])

      res.status(200).json(order);     

    } catch (error) {
      res.status(500).json({error: error.message})
    }
  })

It will group the documents by the category and calculates the total for each category.

Then you sort the categories in descending order by the total, and finally limits the result to only return the first element, which will be the category with the highest total.

CodePudding user response：

Try this: - Playground example - https://mongoplayground.net/p/UEM--RU_UHP

The aggregation phases are fairly self explanatory in the code below.

Note: I've made the assumption that you have to unwind because your products attribute is an array.

db.collection.aggregate([
  {
    $unwind: "$products"
  },
  {
    $group: {
      _id: "$products.productId.category",
      total: {
        $sum: 1
      }
    }
  },
  {
    $sort: {
      total: -1
    }
  },
  {
    $project: {
      _id: 0,
      category: "$_id",
      total: 1
    }
  },
  {
    $limit: 1
  }
])