CODEWITHSUNDEEP
mongodbpymongo
SVill
SVill

Reputation: 439

Pymongo - Finding documents based on timestamp in nested object

I'm trying to retrieve all documents with a timestamp field newer than 7 days ago using pymongo. However the timestamp is held within a nested object, and I'm still getting all events. My code so far is as follows:

mongoUrl = "mongo replica set url"

client = MongoClient(mongoUrl)
database = "testdb"
db = client[database]

#timestamp is in epoch
date_7_days_ago = (datetime.now() - timedelta(days=7)).strftime('%s')

#use $gt, since we want epoch timestamps greater than the one from 7 days ago
output = db.testEvents.find({ "cases": { "$elemMatch": { "timestamp": { "$gt": int(date_7_days_ago) } } } })


print(len(output[0]['cases']))

for x in output:
        pprint(x)

The 'cases' object has the following structure:

{'_id': ObjectId('12345'),
 'cases': [{'assigned': "John Doe",
             'case': 'action'
             'timestamp': 1610152363.8607295}, #I care only about this line here
            {'assigned': "Jane Doe",
             'case': 'action'
             'timestamp': 1610152363.8607295}
          ]
}

I've also tried hard coding the epoch time in the code, but am still getting thousands of events, some of which date back to March 2020. Obviously, I'm not actually reading the timestamp, but I was led to believe that for nested objects like this, I can use $elemMatch.

Upvotes: 0

Views: 534

Answers (2)

Belly Buster
Belly Buster

Reputation: 8814

This code sample creates 20 documents, 1 per day, and the filter only selects the last 8 days.

from pymongo import MongoClient
from datetime import datetime, timedelta

db = MongoClient()['mydatabase']

for i in range(20):
    dt = (datetime.now() - timedelta(days=i)).timestamp()
    db.testEvents.insert_one({
         'cases': [{'assigned': "John Doe",
                     'case': 'action',
                     'timestamp': dt},
                    {'assigned': "Jane Doe",
                     'case': 'action',
                     'timestamp': dt}
                  ]
        })

date_7_days_ago = (datetime.now() - timedelta(days=7)).timestamp()
output = db.testEvents.find({ "cases": { "$elemMatch": { "timestamp": { "$gt": int(date_7_days_ago) } } } })

print(len(list(output)))

prints:

8

Upvotes: 1

Belly Buster
Belly Buster

Reputation: 8814

You should store you date/times as BSON date times; if you are stuck with "UNIX" timestamps, use .timestamp()

date_7_days_ago = (datetime.now() - timedelta(days=7)).timestamp()

Upvotes: 0

Related Questions