MongoDB filter an array field by period (start date, end date) using PHP

Question

I have MongoDB documents with this structure:

{
     _id: 1
     dates: [
         ISODate ("2015-08-21T22: 00: 00Z")
         ISODate ("2015-09-27T22: 00: 00Z")
     ],
     ...
}

In my example, we see that the document 1 was accessed:

2015-08-21
2015-09-27

In my application, I can filter by time period (start date, end date).

If in my application I filter the period "2015-09-01 - 2015-09-01":

Logically the document 1 should not be found because it was not consulted during this period. But with my tests, the document 1 is found.

What conditions must I use to filter the data correctly?

I tried that but it's fallible:

<?php
$begin = new \DateTime('2015-09-01');
$end = new \DateTime('2015-09-01');

$expr1 = $qb->expr()->field('dates')->gte($begin);
$expr2 = $qb->expr()->field('dates')->lte($end);

$qb
    ->addAnd($expr1)
    ->addAnd($expr2)
    ;

Thanks for your help, Yohann

If you are expecting to test against each array element individually then you need [`$elemMatch`](http://docs.mongodb.org/manual/reference/operator/query/elemMatch/). Not sure of the doctrine syntax off-hand and don't have anything to test with right now. But that should give you a pointer. Also correctly tagged your question to make it visible to the right people. — Blakes Seven, Sep 30 '15 at 08:29

Rohit Jain · Answer 1 · 2015-09-30T09:48:47.437

0

With aggregation

This is the code snippet for mongoshell you can try

> db.collection.aggregate([{$unwind:"$dates"},{$match:{"dates":{$gt:new ISODate("2015-01-01"),$lt:new ISOD
ate("2015-09-01")}}}])

With Find Query

db.collection.find({dates:{$elemMatch:{$gt:new ISODate("2015-01-10"),$lt:new ISODate("2015-09-01")}}},{"dates.$":1})

edited Sep 30 '15 at 09:48

answered Sep 30 '15 at 09:39

Rohit Jain

2,052
17
19

score 0 · Accepted Answer · answered Sep 30 '15 at 10:27

Thank you everyone, I solved the problem with your answers, full tested.

Hard way to do this with the doctrine odm, here is the solution:

<?php
$begin = new \DateTime($options['begin']);
$end = new \DateTime($options['end']);

$expr = $qb->expr()
    ->gte($begin)
    ->lte($end)
;

$exprElementMatch = $qb->expr()->field('dates')->elemMatch($expr);
$qb->addAnd($exprElementMatch);

score 0 · Answer 3 · answered Aug 09 '17 at 15:46

The answer currently marked as solved didn't actually work for me.

Given the following data:

{
     _id: 1
     dates: [
         ISODate("2015-08-21T22:01:12Z")
         ISODate("2015-08-22T00:06:45Z")
     ],
     ...
}

The following query matches the document above:

db.collection.find({dates:{$elemMatch:{$gt:new ISODate("2017-08-21"),$lt:new ISODate("2017-08-22")}}}

I was confused and read the $elemMatch documentation and it states:

The $elemMatch operator limits the contents of an field from the query results to contain only the first element matching the $elemMatch condition.

So this simply does not work as expected from this solved question. For my use case I was able to evaluate just the first element in the dates array to filter my documents with the following query.

db.collection.find({"dates.0":{$gte:new ISODate("2017-07-02"),$lt:new ISODate("2017-07-03")})

This answer goes into detail in how to actually query the dates array.

MongoDB filter an array field by period (start date, end date) using PHP

3 Answers3