How to match date in aggregate $match using MongoDB and Laravel 5.2?

Question

I have more than 50 thousand records in mongodb collections. To avoid foreach loop in laravel I am using aggregate in my query.

$start = new MongoDate(strtotime("2015-10-01 00:00:00"));
$result = Pms::raw(function ($collection) use($start){
        return $collection->aggregate(array(
            array( '$project' => array( 'EventTS' => 1, 'MainsPower' => 1, '_id' => 0) ),
            array(
                '$unwind' => array(
                    'path' => '$MainsPower',
                    'includeArrayIndex' => "arrayIndex",
                    'preserveNullAndEmptyArrays' => true
                )
            ),
            array(
                '$match' => array(
                    'EventTS' => array(
                        '$gte' => $start
                    )
                )
            ),                
            array(
                '$project' => array(
                    'MainsPower' => 1,
                    'timestamp' => array(
                        '$add' => array(
                            '$EventTS',
                            array( '$multiply' => array( 60000, '$arrayIndex' ) )
                        )
                    )
                )
            )
        ));
    })->toArray();

I need to compare date with a collection, but if I use below code then my result set returns empty.

 array(
            '$match' => array(
                'EventTS' => array(
                    '$gte' => $start
                )
            )
        )

what's the right way to compare date in mongodb collection inside PHP with Laravel 5.2.

Attached is the Sample Document

{ 
    "_id" : ObjectId("576165f58d8b8f39458b456a"), 
    "EventTS" : ISODate("2000-05-11T05:30:00.000+0000"), 
    "PanelID" : "A01000", 
    "MainsPower" : [
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        NumberInt(147), 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null
    ], 
}

{ 
    "_id" : ObjectId("576165f58d8b8f39458b456b"), 
    "EventTS" : ISODate("2016-06-08T18:30:00.000+0000"), 
    "PanelID" : "A01604", 
    "MainsPower" : [
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null, 
        null
    ]
}

chridam · Accepted Answer · 2016-06-16 11:26:48Z

1

An alternative is to use Carbon's createFromDate() property to create the date range since Laravel also supports Carbon or DateTime objects instead of MongoDate objects which will be converted internally to MongoDate objects when saved to the database.

In the same breadth as above, you would want to restructure your aggregation operation so that the $match filter is first in the pipeline, before the $unwind operation, that way you can optimize your aggregation operation as EventsTS is an independent field from the MainsPower array (at least from the way the code was written).

Also, you won't need the initial project operator, not necessary as further down the pipeline you have another $project operator which is returning just the required fields.

So, applying the Carbon package, you can try the following pipeline:

$start = Carbon::createFromDate(2015, 10, 1);
$result = Pms::raw(function ($collection) use($start){
        return $collection->aggregate(array(
            array(
                '$match' => array(
                    'EventTS' => array(
                        '$gte' => $start
                    )
                )
            ), 
            array(
                '$unwind' => array(
                    'path' => '$MainsPower',
                    'includeArrayIndex' => "arrayIndex",
                    'preserveNullAndEmptyArrays' => true
                )
            ),                           
            array(
                '$project' => array(
                    'MainsPower' => 1,
                    'timestamp' => array(
                        '$add' => array(
                            '$EventTS',
                            array( '$multiply' => array( 60000, '$arrayIndex' ) )
                        )
                    )
                )
            )
        ));
    })->toArray();

answered Jun 16, 2016 at 11:26

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

sandip kakade Over a year ago

I followed your instructions. I removed the initial projection and moved the match query before the unwind. I also tried copy pasting your code but i still get an empty set.@chridam

chridam Over a year ago

One way you can debug a pipeline that's giving you unexpected results is to run the aggregation with just the first pipeline operator. If that gives the expected result, add the next. In the answer above, you'd first try aggregating just the $match; if that works, add the $unwind. This can help you narrow down which operator is causing issues. If the issue is with the $match operator then try changing the date variables in the range query, see if some dates match.

sandip kakade Over a year ago

We tried debugging one element at a time. It seems that any date operation on the query is failing using the above method. It works fine for string and integer comparison. We tried all operators for data comparison and all of them return an empty set. @chridam

chridam Over a year ago

By the way, can you edit your question by providing some sample documents that can be used to replicate the same issue as above?

sandip kakade Over a year ago

uploaded a couple of sample documents.

|

Collectives™ on Stack Overflow

How to match date in aggregate $match using MongoDB and Laravel 5.2?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related