MongoDB limit group by results

Question

My mongo DB has a document with following structure:

{'vname':'x', 'pname': 'xyz', 'price': '10000'}

I want to get all documents which match the pname='xy' and then group by vname and limit results for each vname by 4.

pipeline = [
    {
        '$facet': {
            'v1': [
                {
                    '$match': {'vname': 'v1'}
                },
                {
                    '$sort': {'price': 1}
                },
                {
                    '$limit': 4
                }
            ],
            'v2': [
                {
                    '$match': {'vname': 'v2'}
                },
                {
                    '$sort': {'price': 1}
                },
                {
                    '$limit': 4
                }
            ]
        }
    }
]

docs = Pinfo.objects(pname__icontains='xy').aggregate(pipeline=pipeline)

The other way I see is to run the filter query multiple times for each vname

docs = Pinfo.objects.filter(Q(pname__icontains='xy')&Q(vname__exact='v1')).limit(4)

Any other way to achieve the same? Is using the aggregate and pipeline approach the better way?

Answer 1

You can try,

• $match pname condition
• $sort by pname ascending order (optional)
• $group by vname and push root object in items and make array
• $project to show required fields and get 4 objects using $slice

db.collection.aggregate([
  { $match: { pname: "xy" } },
  { $sort: { pname: 1 } },
  {
    $group: {
      _id: "$vname",
      items: { $push: "$$ROOT" }
    }
  },
  {
    $project: {
      _id: 0,
      vname: "$_id",
      items: { $slice: ["$items", 4] }
    }
  }
])

Playground

---

If you want all objects in root then you can add below pipelines after above pipelines,

• $unwind deconstruct items array to object
• $replaceRoot to replace items object in root

  { $unwind: "$items" },
  { $replaceRoot: { newRoot: "$items" } }

Playground

---

A more option from MongoDB 5.2, using the $topN operator in the $group stage,

db.collection.aggregate([
  { $match: { pname: "xy" } },
  {
    $group: {
      _id: "$vname",
      items: {
        $topN: {
          output: {
            pname: "$pname",
            price: "$price"
          },
          sortBy: { price: 1 },
          n: 4
        }
      }
    }
  }
])

Playground