seldom used but powerful aggregation operators

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.

2. $add and $subtract

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.

$ifNull:

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.

Advertisements

One thought on “seldom used but powerful aggregation operators

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s