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

Question

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

Jatin · Accepted Answer · 2022-03-17 13:31:43Z

2

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.-

answered Mar 17, 2022 at 13:31

Jatin

563 bronze badges

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

Comments

MauriRamone · Accepted Answer · 2022-03-15 19:49:51Z

1

try with this

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

edited Mar 15, 2022 at 19:49

answered Mar 15, 2022 at 19:44

MauriRamone

5011 gold badge5 silver badges23 bronze badges

3 Comments

Mukul Varshney Over a year ago

Hi @MauriRamone thanks for your input but this one I have tried already and it also does not provide the required output.

MauriRamone Over a year ago

Hi, I have noted that outerTimeStamp is set as String: "14-Mar-2022". Try with Date format, for example ISODate("2022-03-15T00:00:00.000Z")

Mukul Varshney Over a year ago

nope! it does not work

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related