Spring Data MongoDB Date between two Dates

Question

i'm using Spring Data for MongoDB and got the following classes

class A {
    List<B> b;
}

class B {
    Date startDate;
    Date endDate;
}

when i save an object of A it gets persisted like

{
    "_id" : "DQDVDE000VFP8E39",
    "b" : [
          {
              "startDate" : ISODate("2009-10-05T22:00:00Z"),
              "endDate" : ISODate("2009-10-29T23:00:00Z")
          },
          {
              "startDate" : ISODate("2009-11-01T23:00:00Z"),
              "endDate" : ISODate("2009-12-30T23:00:00Z")
          }
    ]
}

Now i want to query the db for documents matching entries in b where a given date is between startDate and endDate.

Query query = new Query(Criteria.where("b").elemMatch(
    Criteria.where("startDate").gte(date)
    .and("endDate").lte(date)
);

Which results in the following mongo query:

{
   "b": {
       "$elemMatch": { 
           "startDate" : { "$gte" : { "$date" : "2009-11-03T23:00:00.000Z"}}, 
           "endDate" : { "$lte" : { "$date" : "2009-11-03T23:00:00.000Z"}}
       }
   }
}

but returns no resulting documents. Does anybody know what i'm doing wrong? I don't get it...

Thank you very much in advance!!

What's "margins" doing in that mongo query? Is that something specific to the Java driver? I don't see that anywhere in your data. — JohnnyHK
– JohnnyHK, Commented Sep 7, 2012 at 0:41
Yes, it doesn't correspond to the Spring Data query, which is also incorrect. You should be querying where("b"), not where("a"). — jyemin
– jyemin, Commented Sep 7, 2012 at 2:07
Im sorry guys, it was late yesterday. I removed the two copy n paste errors! — sics
– sics, Commented Sep 7, 2012 at 4:24
The one document you show doesn't satisfy the query, are there others that do? Should your $gte and $lte terms be swapped. — JohnnyHK
– JohnnyHK, Commented Sep 7, 2012 at 4:29
@sics, which version of the spring-data-mongodb are you using? — Ghasfarost
– Ghasfarost, Commented Feb 22, 2013 at 21:53

JohnnyHK · Accepted Answer · 2012-09-07 04:34:02Z

13

If you want to find docs where date is between the startDate and endDate of a b array element then you need to reverse your gte and lte calls:

Query query = new Query(Criteria.where("b").elemMatch(
    Criteria.where("startDate").lte(date)
    .and("endDate").gte(date)
);

answered Sep 7, 2012 at 4:34

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

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

3 Comments

sics Over a year ago

Oh my gosh, notice to brain: coding after 12 hours of work (also coding) is not good :) thank you for helping me out with this - beginner's mistake!

Kevin Meredith Over a year ago

side question - how would you index with the startDate and endDate fields? My understanding is that, when indexing, the "range" field is positioned as last in the compound-index. Example: "Find all men with ages between 50 and 75". With gender and age fields, you'd make a compound index on gender-age. (cookbook.mongodb.org/patterns/date_range )

Sumanth Varada Over a year ago

Can we do that if those dates are in string form ?

Anthon · Accepted Answer · 2013-05-06 05:22:01Z

2

{"created_at":{$gt:ISODate("2013-04-30T00:00:00Z"),$lt:ISODate("2013-04-30T23:59:59Z")}}

edited May 6, 2013 at 5:22

Anthon

78.3k35 gold badges207 silver badges290 bronze badges

answered May 6, 2013 at 5:02

Simon Liu

4835 silver badges13 bronze badges

1 Comment

ryber Over a year ago

While this is correct from a purely mongo standpoint the question was about spring-data-mongo's API

Collectives™ on Stack Overflow

Spring Data MongoDB Date between two Dates

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related