Query to search specific nested list of JSON data in mongoDB using python-CodePudding

I have Docs in mongo DB in the following format - Here outerTimestamp is in string format and Price is int format

{
   "_id":"ObjectId(""622f950e73043487031bb3ee"")",
   "outerTimeStamp" : "14-Mar-2022",
   "filtered":{
      "data":[
         {
            "Price":14350,
            "expiryDate":"17-Mar-2022",
            "info1":{
               "Price":14350,
               "expiryDate":"17-Mar-2022",
            },
            "info2":{
               "Price":14350,
               "expiryDate":"17-Mar-2022"
            }
         },
         {
            "Price":14350,
            "expiryDate":"17-Mar-2022",
            "info1":{
               "Price":14350,
               "expiryDate":"17-Mar-2022",
            },
            "info2":{
               "Price":14350,
               "expiryDate":"17-Mar-2022"
            }
         },
         ......
         ....
     ]
    }
}

My requirement is I need all the list elements [named data] having price == 14350 and anemphasized text entire document should be having date less than outerTimeStamp (17-Mar-2022)

I am using the following query but it not giving me the desired output.

data = db.find({
    '$and' : 
            [
                {'outerTimeStamp ': { '$lt': "15-Mar-2022" }},
                {'filtered.data': { '$elemMatch': { 'Price': 14350 }}}
            ]
        }, 
    {'filtered.data' : 1}
)

I did following command as well but this one is not returning anythign at all -

 import dateutil
dateStr = '2022-03-15T00:00:00.000Z'
myDatetime = dateutil.parser.parse(dateStr)

data = db.find({
    '$and' : 
            [
                {'outerTimestamp': { '$lt': myDatetime }},
                {'filtered.data.Price': 15000 }
            ]
        }, 
    {'filtered.data' : 1})

by this still, I am getting all the data elements, though this query is respecting outerTimeStamp condition [ {'outerTimeStamp ': { '$lt': "15-Mar-2022" }} ]

I will be grateful for the help

regards

CodePudding user response：

Your query just needs a small change:

data = db.find({
'$and' : 
        [
            {'outerTimeStamp ': { '$lt': "15-Mar-2022" }},
            {'filtered.data': { '$elemMatch': { 'Price': 14350 }}}
        ]
    }, 
    {'filtered.data.$' : 1}
)

Ref: https://docs.mongodb.com/manual/reference/operator/projection/positional/#mongodb-projection-proj.-

CodePudding user response：

try with this

data = db.find({
      '$and' : [{'outerTimeStamp': { '$lt': "15-Mar-2022" }},
                {'filtered.data.Price': 14350 } ]}, 
      {'filtered.data' : 1})