CODEWITHSUNDEEP
databasemongodbmongodb-queryaggregation-framework
Kedel Mihail
Kedel Mihail

Reputation: 151

MongoDb count percent of document with a certain field present

I have some MongoDb document's(representing orders) and their schema looks roughly like that:

{
   id: ObjectID
   exchange_order_products: Array
}

The exchange_order_products array is empty if the customer didn't exchange any items he ordered, or if they did, the array will contain an Object for each item exchanged.

I want to get the percent of orders in which the customer didn't exchange anything, e.g. exchange_order_products array is empty.

So basically the formula is the following: (Number Of Orders With At Least One Exchange * 100) / Number of Orders With No Exchanges

I know that I can count the number of orders where the exchange_order_products array is empty like that:

[{$match: {
   exchange_order_products: {$exists: true, $size: 0}
}}, {$count: 'count'}]

But how do I simultaneously get the number of all the documents in my collection?

Upvotes: 1

Views: 407

Answers (1)

mickl
mickl

Reputation: 49975

You can use $group and $sum along with $cond to count empty and non-empty ones separately. Then you need $multiply and $divide to calculate the percentage:

db.collection.aggregate([
    {
        $group: {
            _id: null,
            empty: { $sum: { $cond: [ { $eq: [ { $size: "$exchange_order_products" }, 0 ] }, 1, 0 ] } },
            nonEmpty: { $sum: { $cond: [ { $eq: [ { $size: "$exchange_order_products" }, 0 ] }, 0, 1 ] } },
        }
    },
    {
        $project: {
            percent: {
                $multiply: [
                    100, { $divide: [ "$nonEmpty", "$empty" ] }
                ]
            }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions