MongoDB: how to query based on date interval and one more condition that match all the date in interval

Question

I have a mongo collection which stores information about products & it has an embedded array that stores its availability datewise.

{
  "product_id": "A",
  "name": "mountain bicycle",
  "cost_per_hour": "$5",
  "availability":[
    {
      "timestamp": ISODate("2018-11-19 18:30:00.000Z"),
      "available": true
    },
    {
      "timestamp": ISODate("2018-12-20 18:30:00.000Z")
      "available": true
    },
    {
      "timestamp": ISODate("2018-12-21 18:30:00.000Z")
      "available": false
    }
  ]
}

I want to list all the products that are available on all dates in a given date interval.

Example: If I query for date between ISODate("2018-11-19 18:30:00.000Z") To ISODate("2018-12-20 18:30:00.000Z") AND available: true, I should get a product with "product_id": "A", since its available on all dates between the date interval.

But If I query between ISODate("2018-11-19 18:30:00.000Z") To ISODate("2018-12-21 18:30:00.000Z") AND available: true, I should NOT get any results since the product is NOT available on all dates in the date range.

I tried using $elemMatch but that returns products that are available in at least 1 of the dates given in the interval, which I don't want.

Please guide.

I don't think its a duplicate. The post you have mentioned talks about projection, where as mine deals with querying embedded mongo array on multiple conditions & returning documents that match all the condition. — user3561281
– user3561281, Commented Nov 18, 2018 at 17:31

JohnnyHK · Accepted Answer · 2018-11-18 17:51:15Z

2

To find documents where all of an array field's elements pass a query, you can invert your query to look for the failure case (available: false), and then use $not to only return the documents where that failure case doesn't occur:

db.test.find({
  availability: {$not: {$elemMatch: {
    timestamp: {$gte: ISODate("2018-11-19 18:30:00.000Z"), 
                $lte: ISODate("2018-12-21 18:30:00.000Z")},
    available: false
  }}}
})

answered Nov 18, 2018 at 17:51

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

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

1 Comment

user3561281 Over a year ago

Thanks @JohnnyHK. How can we do this for inversion for top-level documents i.e documents that are not a part of the array ?

AvrahamL · Accepted Answer · 2018-11-18 18:45:52Z

-1

Folowing the documentation on:

https://docs.mongodb.com/manual/tutorial/query-array-of-documents/

at:

db.inventory.find( { "instock": { warehouse: "A", qty: 5 } } )

What you are looking for is something like:

db.StoreCollection.find( { 
         "availability": { 
              "timestamp": ISODate("2018-12-20 18:30:00.000Z")
              "available": true
          }
 })

answered Nov 18, 2018 at 18:45

AvrahamL

3115 silver badges17 bronze badges

2 Comments

Neil Lunn Over a year ago

Keep reading the documentation there and learn something. Specify Multiple Conditions for Array of Documents. You're query here is completely wrong. You also appear to have misread the question.

user3561281 Over a year ago

Yes I read the docs before. The above query which you have suggested will only work if the document given in query exactly matches the document structure & order present in the collection you are querying. That is not the intended result am looking at. I am querying based on only 2 fields timestamp & available field.

Collectives™ on Stack Overflow

MongoDB: how to query based on date interval and one more condition that match all the date in interval

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related