1

In mongodb,

if I have a date, and I want to query for records that have a date later than the provided date I can do this:

collection.find({datefield:{$gt:somedate}})

And if I want to find records between a date range, I can do an $and with lt and gt.

But say that my datefield is actually datefields, a list of dates. And say that there are the following records for datefield

datefields = [june 1, 2015 hhmmss, june 2, 2015 hhmmss, june 14, 2015 hhmmss]
datefields = [june 1, 2015 hhmmss, june 3, 2015 hhmmss, june 8, 2016 hhmmss, june 17, 2015 hhmmss]

How would I construct the search to fetch for all records whose dates are between say june 3 and june 7, so that I only get the second record. And if ranges are not possible, can I do just a single search for june 3, 2015 while disregarding the hhmmss?

Stupid.Fat.Cat
  • 10,755
  • 23
  • 83
  • 144

1 Answers1

2

I think this will solve what you're asking. Use an aggregate operation to unwind your arrays and treat each element as a separate value in a document. 

db.collection.aggregate( [
{ $unwind : { "$datefields" } },
{ $match : { datefields : { $gt : ISODate("2015-06-02T00:00:01Z"), $lte : ISODate("2015-06-03T23:59:59Z")} } }
])

That alone will give you an "exploded view" of your array results, with one document per array element that matches. If you want to collapse it down you can add a $group stage.

If this isn't what you're asking could you give a bit more detail?

For the second half of your question regarding "disregarding the hhmmss", there are a number of questions already answered on SO about that. Check out Query Mongodb on month, day, year... of a datetime

Community
  • 1
  • 1
womp
  • 115,835
  • 26
  • 236
  • 269
  • Yes! I did not know of aggregate or unwind, this is what I need. So is aggregate essentially a subcollection? – Stupid.Fat.Cat Jul 16 '15 at 17:05
  • 1
    No.... aggregate is a special branch of processing. It's called the "aggregation pipeline" and it lets you do all kinds of piped operations to filter a collection of documents in various ways. Check out the documentation and some tutorials on it, it's very powerful. http://docs.mongodb.org/manual/core/aggregation-introduction/ – womp Jul 16 '15 at 17:08
  • Quick question, should the outermost {} be [] instead? And if I'm getting weird errors like "Cannot encode object: set(["Dates"]), is this a data integrity issue? – Stupid.Fat.Cat Jul 16 '15 at 17:22
  • Oh yeah, sorry. Too much heads down C# today. Also missing a brace on the $unwind. I'll edit. – womp Jul 16 '15 at 18:13
  • could you please mention date format to use i.e; `somedate` and `someotherdate` above – Shiva Jul 16 '15 at 19:48
  • Yep... I'm not real familiar with python but I'm assumign you can use the ISODate() object constructor. There are a few ways to pass values into it, I've edited to show a common way. If you're in the mongo shell you can always use javascript dates as well. – womp Jul 16 '15 at 19:57
  • @womp you can just use a datetime constructor, like so: start_date = datetime(2015, 7, 1, 0, 0, 0) – Stupid.Fat.Cat Jul 20 '15 at 19:31