pymongo db query with multiple conditions- $and $exists

Question

An example document looks like this

 {
    "_id":ObjectId("562e7c594c12942f08fe4192"),
    "Type": "f",
    "runTime": ISODate("2016-12-21T13:34:00.000+0000"), 
    "data" : {
       "PRICES SPOT" : [
        {
            "value" : 29.64, 
            "timeStamp" : ISODate("2016-12-21T23:00:00.000+0000")
        }, 
        {
            "value" : 29.24, 
            "timeStamp" : ISODate("2016-12-22T00:00:00.000+0000")
        },  
        {
            "value" : 29.81, 
            "timeStamp" : ISODate("2016-12-22T01:00:00.000+0000")
        }, 
        {
            "value" : 30.2, 
            "timeStamp" : ISODate("2016-12-22T02:00:00.000+0000")
        }, 
        {
            "value" : 29.55, 
            "timeStamp" : ISODate("2016-12-22T03:00:00.000+0000")
        }
     ]
   }
}

My MongoDb has different Type of documents, I'd like to get a cursor for all of the documents that are from a time range that are of type: "f" but that actually exist. There are some documents in the database that broke the code I had previously(which did not check if PRICES SPOT existed).

I saw that I can use $and and $exists from the documentation. However, I am having trouble setting it up because of the range, and the nesting. I am using pyMongo as my python driver and also noticed here that I have to wrap the $and and $exists in quotes.

My code

def grab_forecast_cursor(self, model_dt_from, model_dt_till):

   # create cursor with all items that actually exist

   cursor = self._collection.find(
        { 
            "$and":[
                {'Type': 'f', 'runTime': {"$gte": model_dt_from, "$lte": model_dt_till}
            ['data']['PRICES SPOT': "$exists": true]}
        ]})

   return cursor

This results in a Key Error it cannot find data. A sample document that has no PRICE SPOT looks exactly like the one I posted in the beginning, just without that respectively.

In short.. Can someone help me set up a query in which I can grab a cursor with all the documents of a certain type but that actually have respected contents nested in.

Update

I added a comma after the model_dt_till and have now a syntax error.

def grab_forecast_cursor(self, model_dt_from, model_dt_till):

   # create cursor with all items that actually exist

   cursor = self._collection.find(
      { 
        "$and":[
           {'Type': 'f', 'runTime': {"$gte": model_dt_from, "$lte": model_dt_till},
                ['data']['PRICES SPOT': "$exists": true]}
    ]})

   return cursor

Neil Lunn · Accepted Answer · 2018-05-24 07:50:09Z

You're trying to use Python syntax to denote the path to a data structure, but the "database" want's it's syntax for the "key" using "dot notation":

 cursor = self._collection.find({
   "Type": "f", 
   "runTime": { "$gte": model_dt_from, "$lte": model_dt_till },
   "data.PRICES SPOT.0": { "$exists": True }
 })

You also don't need to write $and like that as ALL MongoDB query conditions are already AND expressions, and part of your statement was actually doing that anyway, so make it consistent.

Also the check for a "non-empty" array is 'data.PRICES SPOT.0' with the added bonus that not only do you know it "exists", but also that it has at least one item to process within it

Python and JavaScript are almost identical in terms of object/dict construction, so you really should be able to just follow the general documentation and the many samples here that are predominantly JavaScript.

I personally even try to notate answers here with valid JSON, so it could be picked up and "parsed" by users of any language. But here, python is just identical to what you could enter into the mongo shell. Except for True of course.

See "Dot Notation" for an overview of the syntax with more information at Query on Embedded / Nested Documents

Collectives™ on Stack Overflow

pymongo db query with multiple conditions- $and $exists

My code

Update

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

My code

Update

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related