使用PyMongo根据嵌套creation_date筛选MongoDB日期范围文档

Alright, let's walk through how to pull documents from your collection where the record_status.creation_date falls within a specific date range using PyMongo.

First, a quick recap of your document structure to make sure we're on the same page:

{
"_id" : ObjectId("5b8a199cf4a98b075490bb47"),
"Pls_Customer_Id" : "C1810151",
"FirstName" : "SCHNEIDER ELECTRIC INDIA PVT LTD",
"LastName" : "SCHNEIDER ELECTRIC INDIA PVT LTD",
"Address1" : "DELHI",
"State" : "DELHI",
"MobileNumber" : "989898989898",
"DeletedFlag" : false,
"Country" : "India",
"record_status" : {
"created_user" : "uploadBatchProgram",
"creation_date" : ISODate("2018-09-01T04:46:20.055Z")
},
"PoliciesRef" : [ ObjectId("5b8a199cf4a98b075490bb45") ],
"__v" : 0,
"customerType" : "Regular"
}

Step 1: Set Up PyMongo and Connect to Your Database #

First, make sure you've got PyMongo installed, then connect to your MongoDB instance and target collection:

from pymongo import MongoClient
from datetime import datetime, timezone

# Connect to MongoDB (adjust the URI if you're using authentication or a remote cluster)
client = MongoClient("mongodb://localhost:27017/")
db = client["your_database_name"]  # Replace with your actual DB name
collection = db["your_collection_name"]  # Replace with your actual collection name

Step 2: Define Your Date Range #

MongoDB stores creation_date as an ISODate (UTC), so you'll want to create UTC datetime objects for your start and end dates. If your input dates are in local time, convert them to UTC to avoid mismatches.

For example, let's say we want documents created between 2018-08-01 and 2018-09-30 (inclusive):

# Start date: August 1, 2018 00:00:00 UTC
start_date = datetime(2018, 8, 1, tzinfo=timezone.utc)
# End date: September 30, 2018 23:59:59 UTC
end_date = datetime(2018, 9, 30, 23, 59, 59, tzinfo=timezone.utc)

Step 3: Build the Query Filter #

Since creation_date is a nested field under record_status, we use the dot notation (record_status.creation_date) in our query. We'll use MongoDB's $gte (greater than or equal) and $lte (less than or equal) operators to define the range:

query = {
    "record_status.creation_date": {
        "$gte": start_date,
        "$lte": end_date
    }
}

Step 4: Execute the Query and Retrieve Results #

Now run the find() method with your query, and iterate over the results:

# Fetch matching documents
matching_docs = collection.find(query)

# Print or process the results
for doc in matching_docs:
    print(doc)
    # Add your processing logic here (e.g., save to a file, analyze data)

Key Notes to Remember #

• Timezone Awareness: Always use UTC datetime objects when querying ISODate fields. If you use local time without timezone info, MongoDB will interpret it as UTC, which could lead to incorrect results.
• Alternative Operators: If you don't want to include the start/end dates, use $gt (greater than) and $lt (less than) instead.
• Indexing: If your collection is large, consider adding an index on record_status.creation_date to speed up query performance:

  collection.create_index("record_status.creation_date")
  

内容的提问来源于stack exchange，提问作者John Bailey