Home > Software design >  How to count number of documents in aggregate pipeline query?
How to count number of documents in aggregate pipeline query?

Time:07-03

So basically I am performing a query using an aggregation pipeline.

In one of the stages, I want to count the number of documents BUT rather than passing the count value to next stage, I'd like to pass the count AND the document(s) that was the input to the count stage.

Current workflow:

  1. $match stage: get documents whose sellerId is a passed in parameter.
  2. $count stage: count the number of documents found in the $match stage.

Current output: {"numDocs": 22}

Desired output: [{"sellerId": ..., "numDocs": 22}, {"sellerId": ..., "numDocs": 22}, ...]

The idea is to get the number of documents but to also get the input document that went into the $count stage. This could be like the desired output where numDocs is appended to each document or is a separate field in the output array.

How can I do this?

**Side note:**I realize this is pretty simple and I could do this use db.collection.Find() and db.collection.Find().Count() but this is just an example. The real query has many more stages.

Thank you!

CodePudding user response:

You can use $facet, to calculate counts and store documents in an array, then unwind the array, and set the numCount field for each document, something like this:

db.collection.aggregate([
  {
    "$facet": {
      "counts": [
        {
          $count: "numDocs"
        }
      ],
      "doc": [
        {
          "$match": {}
        }
      ]
    }
  },
  {
    "$unwind": "$doc"
  },
  {
    "$unwind": "$counts"
  },
  {
    "$addFields": {
      "doc.numCount": "$counts.numDocs",
      
    }
  },
  {
    "$project": {
      counts: 0
    }
  }
]);

Here's the playground [link]1.

CodePudding user response:

the proper way to solve it is to use $setWindowFields since $facet and $push has 16MB size limit

if you have mongodb v5 or higher you can use $setWindowFields like this

test it here

db.collection.aggregate([
  {
    $setWindowFields: {
      partitionBy: {
        sellerId: "$sellerId"
      },
      output: {
        numDoc: {
          $sum: 1,
          window: {
            documents: [
              "unbounded",
              "unbounded"
            ]
          }
        }
      }
    }
  }
])

otherwise you can use $group and $push

test it here

db.collection.aggregate([
  {
    "$group": {
      "_id": "$sellerId",
      "numDocs": {
        "$sum": 1
      },
      "docs": {
        "$push": "$$ROOT"
      }
    }
  }
])
  • Related