CODEWITHSUNDEEP
ruby-on-railsrubymongodbaggregation-frameworkmoped
JayNCoke
JayNCoke

Reputation: 1091

Mongoid aggregation $match with Date objects not working?

I have User documents in a collection that have this structure:

{ "_id" : ObjectId( "4fb54ef46d93b33b21003951" ),
  "activities" : [ 
    { "id" : ObjectId( "4fd66f9001e7fe9f03000065" ),
      "type" : "checkin",
      "date_time_created" : Date( 1339453328000 )}, 
    { "date_time_created" : Date( 1337351732000 ),
      "date_time_updated" : Date( 1337351952635 ),
      "id" : ObjectId( "4fb65e346d93b3fe77000000" )}
  ]
}

I can easily query these documents based on date:

User.where( 
  :activities => { 
    '$elemMatch' => {
      :date_time_created => { '$gte' => start_date, '$lt' => end_date }
    }
  } 
).length

According to logs:

MOPED: 127.0.0.1:27017 COMMAND database=db command={:count=>"users", :query=>{"activities"=>{"$elemMatch"=>{"date_time_created"=>{"$gte"=>2012-05-10 00:00:00 UTC, "$lt"=>2012-07-12 00:00:00 UTC}}}}} (0.5260ms)

I get the results I need this way.

However, when I'm trying to use the new aggregate function and $match based on the same criteria:

User.collection.aggregate( [
  { "$match" => {
    :activities => {
      '$elemMatch' => {
        :date_time_created => { '$gte' => start_date, '$lt' => end_date }
      }
    }
  } }
]).length

According to logs:

MOPED: 127.0.0.1:27017 COMMAND database=db command={:aggregate=>"users", :pipeline=>[{"$match"=>{:activities=>{"$elemMatch"=>{"date_time_created"=>{"$gte"=>Thu, 10 May 2012, "$lt"=>Thu, 12 Jul 2012}}}}}]} (0.6049ms)

"start_date" and "end_date" are Ruby Date objects and are essentially the same in both queries. However, when I look at the logs they are changed into different formats. When I try to force the format with something like start_date.strftime("%Y-%m-%d") it still doesn't work.

There are other functions in the aggregate pipeline, but I took them out and I still get the error.

How can I get the aggregate function working to match on Dates?

Upvotes: 1

Views: 3992

Answers (4)

henrik242
henrik242

Reputation: 386

I found this short post, that helped me. Here is the essential code example - seems very simple in comparison:

Stat.collection.aggregate({
  '$match' => {
    '$and' => [
      { 'day' => { '$gte' => from.to_date.mongoize } },
      { 'day' => { '$lte' => to.to_date.mongoize } }
    ]
  },
})

Creds to the author!

Upvotes: 0

ThienSuBS
ThienSuBS

Reputation: 1622

Correct type for match in aggregate like this:

{:$match=>{ :created_at=>{:$gte=>2017-03-07 00:36:30 +0700}}}

This mean you should using method:

match_na = {
  '$match': {created_at: { '$gte': (Time.now-30.days) } }
}

Please noticed: Don't use Time.zone.now (because output it will be another format). I'm pretty sure about 100%. It takes me 3 hours.

Again: Make format of Date or Time like this:

2017-03-07 00:36:30 +0700

Not this:

Mon, 03 Apr 2017 00:34:14 ICT +07:00

OR easy way just: use method mongoize

(Date.today - 30.days).mongoize

Upvotes: 0

Gary Murakami
Gary Murakami

Reputation: 3402

The following test uses the aggregation framework to approximate the logical equivalent of your query.

First $unwind the array, $match, then $group using $addToSet in case there are multiple array elements that match. Note that this is a lossy process, but this is acceptable if you only need the count or the ids.

Hope that this helps.

test/unit/user_test.rb

require 'test_helper'

class UserTest < ActiveSupport::TestCase
  def setup
    User.delete_all
  end

  test "user aggregate" do
    User.create(
        activities: [
          { id: Moped::BSON::ObjectId("4fd66f9001e7fe9f03000065"),
            type: "checkin",
            date_time_created: Time.at(1339453328000) },
          { date_time_created: Time.at(1337351732000),
            date_time_updated: Time.at(1337351952635),
            id: Moped::BSON::ObjectId("4fb65e346d93b3fe77000000") }
        ]
    )
    start_date = Time.at(1339453328000)
    end_date = start_date + 24*60*60
    assert_equal(1, User.where(
        :activities => {
            '$elemMatch' => {
                :date_time_created => { '$gte' => start_date, '$lt' => end_date }
            }
        }
    ).length)
    assert_equal(1, User.collection.aggregate( [
        { '$unwind' => '$activities' },
        { '$match' => { '$and' => [
            { 'activities.date_time_created' => { '$gte' => start_date } },
            { 'activities.date_time_created' => { '$lt' => end_date } }
        ] } },
        { '$group' => { '_id' => '$_id', 'activities' => { '$addToSet' =>  '$activities' } } }
    ] ).length)
  end
end

Upvotes: 7

Raxit Sheth
Raxit Sheth

Reputation: 2601

m not much into ruby, but if you can paste mongo shell, i can help.

there are few tips: 1. you are using elemMatch so you should use $unwind 2. after that you try to use $match

Upvotes: -3

Related Questions