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?

user_8987128 · Answer

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

MongoDB limit group by results

1 Answers