Most programmers who use MongoDB may have heard about its aggregation framework but only a few have used it to its full potential. Recently, in one of our projects, we got a complex requirement where the user needed to generate a report of sales done for any day, month or year. Using just MongoDB queries would have been very slow compared to the aggregation framework. So I decided to take up the challenge and learn the aggregation operators and make my code awesome! Here are the operators that helped me:
# example we would refer throughout our post:
class Booking
include Mongoid::Document
include Mongoid::TimeStamp
field :agreement_amount, type: Float, default: 0.0
field :project_name, default: ''
field :is_active, type: Boolean, default: false
field :amount_collected, type: Float, default: 0.0
field :amount_refunded, type: Float, default: 0.0
field :amount_dishonored, type: Float, default: 0.0
field :due_installment_amount, type: Float, default: 0.0
belongs_to :offer
end
1. $and
$and is one of the MongoDB aggregation conditional operators where you can specify multiple conditions. Any document which satisfies all these conditions would get forwarded to next stage in the pipeline which is nothing but $group stage. Here is an example
# Today's sales value
{
'$project' =>
'todaysales' =>
{ '$cond' =>
[
{ '$and' =>
[
{ '$gte' => ['$created_at', Time.zone.now.beginning_of_day.mongoize] },
{ '$lte' => ['$created_at', Time.zone.now.end_of_day.mongoize] }
{ '$eq' => ['$is_cancelled', false] }
]
}, '$agreement_amount', 0
]
},
}
}
The above example returns the $agreement_amount if it is today’s booking.
Similar to conditional operators, we have arithmetic operators where you can do addition or subtraction. Here is an example:
{
'$project' =>
'yearlycollection' =>
{ '$cond' =>
[
{ '$and' =>
[
{ '$gte' => ['$created_at', Time.zone.now.beginning_of_year.mongoize] },
{ '$lte' => ['$created_at', Time.zone.now.end_of_day.mongoize] }
{ '$eq' => ['$is_cancelled', false] }
]
}, "$subtract" => [ { "$amount_collected", "$add" => [ "$amount_refunded", "$amount_dishonoured" ] } ] }, 0
]
},
}
}
In the above example, if the condition has been satisfied then it adds the $amount_refunded and $amount_dishonored and subtracts the result from the $amount_collected, otherwise it returns 0.
This is similar to the conditional operator with a short syntax. It returns the value if value is null. Here is an example:
{
$project: {
due_installment_amount: { $ifNull: [ "$offer_id", "$due_installment_amount" ] }
}
}
In the above example if offer_id is Null, only then does it return the due_installment_amount of that document.
4. $let:
Can we store an aggregated value in a variable and use in next expression? Yes! $let lets us do just that 🙂
{
'$project' =>
{
'monthlycollection' =>
{
'$let' => {
collection: {
$cond' =>
[
{ '$and' =>
[
{ '$gte' => ['$created_at', Time.zone.now.beginning_of_year.mongoize] },
{ '$lte' => ['$created_at', Time.zone.now.end_of_day.mongoize] }
{ '$eq' => ['$is_cancelled', false] }
]
}, "$subtract" => [ { "$amount_collected",
"$add" => [ "$amount_refunded", "$amount_dishonored" ] } ] }, 0
]
}
}
in: { $cond: { '$gt': ['$$collection', 0] }, '$$collection', 0 }
}
}
}
In the above example, if amount_collected - (amount_refunded + amount_dishonored) is less than 0 for a particular booking then it returns 0 instead of -ve value. Notice that $$ is the convention to access variables within the aggregation pipeline.
Most of these seem like standard conditional operators but their uses are phenomenal. From MongoDB 3.2 onwards, aggregation framework provides next generation mongodb operators like $lookup (performs left outer join), $switch and others that we shall discuss in my next post.
Any suggestions would be welcome.
rails learning 