CODEWITHSUNDEEP
mongodbaggregation-frameworkaggregate
Djinii
Djinii

Reputation: 31

Total count and field count with condition in a single MongoDB aggregation pipeline

I have a collection of components. Simplified, a document looks like this:

{
    "_id" : "50c4f4f2-68b5-4153-80db-de8fcf716902",
    "name" : "C156",
    "posX" : "-136350",
    "posY" : "-27350",
    "posZ" : "962",
    "inspectionIsFailed" : "False"
}

I would now like to calculate three things. The number of all components in the collection, the number of all faulty components "inspectionIsFailed": "True" and then the ratio (number of all faulty components divided by the number of all components). I know how to get the first two things separately and in a row with one aggregation each.

Number of all components:

db.components.aggregate([
    {$group: {_id: null, totalCount: {$sum: 1}}}
]);

Number of all faulty components:

db.components.aggregate([
    {$match: {inspectionIsFailed: "True"}},
    {$group: {_id: null, failedCount: {$sum: 1}}}
]);

However, I want to calculate the two values in a single pipeline and not separately. Then I could use $divide to calculate the ratio at the end of the pipeline. My desired output should then only contain the ratio:

{ ratio: 0.2 }

My problem with a single pipeline is:
If I try to calculate the total number first, then I can no longer calculate the number of the faulty components. If I first calculate the number of faulty components with $match, I can no longer calculate the total number.

Upvotes: 1

Views: 1915

Answers (2)

Ashkan
Ashkan

Reputation: 1378

As I found out, you can not do it in one pipeline, then you have to use $facet as in this answer explained.

Also I suggest to use boolean for inspectionIsFailed.

db.collection.aggregate([
  {
    $facet: {
      totalCount: [
        {
          $count: "value"
        }
      ],
      pipelineResults: [
        {
          $match: {
            inspectionIsFailed: true
          }
        },
        {
          $group: {
            _id: "$_id",
            failedCount: {
              $sum: 1
            }
          }
        }
      ]
    }
  }
])

You can test it here.

Upvotes: 1

turivishal
turivishal

Reputation: 36104

You can try,

  • $group by null, get totalCount with $sum, and get failedCount on the base of $cond (condition) if inspectionIsFailed id True then return 1 and sum other wise 0
  • $project to get ratio using $divide
db.collection.aggregate([
  {
    $group: {
      _id: null,
      totalCount: { $sum: 1 },
      failedCount: {
        $sum: {
          $cond: [{ $eq: ["$inspectionIsFailed", "True"] }, 1, 0 ]
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      ratio: {
        $divide: ["$failedCount", "$totalCount"]
      }
    }
  }
])

Playground

Upvotes: 3

Related Questions