如何查询MongoDB中嵌套数组最新首条元素状态为opened的文档

Hey there! The core issue here is that your current query checks if any event matches the status, but you need to validate only the most recent event (sorted by date) in the events array. Let’s break down how to solve this with the MongoDB C# driver, depending on your MongoDB version:

Modern Approach (MongoDB 5.0+) #

MongoDB 5.0 introduced the $sortArray operator, which simplifies this task drastically. We’ll use an aggregation pipeline to sort the events array, isolate the latest entry, and filter based on its status.

Here’s the C# implementation:

// Build the aggregation pipeline
var pipeline = new BsonDocument[]
{
    // Optional: Keep your existing regex filter as the first stage
    new BsonDocument("$match", new BsonDocument(
        "events.event", new BsonRegularExpression(searchString, "i")
    )),
    // Add a temporary field with events sorted by date (newest first)
    new BsonDocument("$addFields", new BsonDocument(
        "latestEvent", new BsonDocument("$first", 
            new BsonDocument("$sortArray", new BsonDocument
            {
                { "input", "$events" },
                { "sortBy", new BsonDocument("date", -1) } // -1 = descending order
            })
        )
    )),
    // Filter only documents where the latest event's status is "opened"
    new BsonDocument("$match", new BsonDocument(
        "latestEvent.status", "opened"
    )),
    // Optional: Clean up results by removing the temporary field
    new BsonDocument("$project", new BsonDocument("latestEvent", 0))
};

// Execute the aggregation
var results = await collection.Aggregate<Notification>(pipeline).ToListAsync();

Step-by-Step Breakdown: #

• $sortArray: Reorders the events array so the most recent entry (highest date value) is first.
• $first: Extracts that top (latest) event into a temporary latestEvent field.
• $match: Filters out any document where latestEvent.status isn’t "opened".
• $project: Optional cleanup to remove the temporary field from your final results.

Alternative for Older MongoDB Versions (Pre-5.0) #

If you’re stuck on a version before 5.0, use $unwind, $sort, and $group to isolate the latest event per document:

var pipeline = new BsonDocument[]
{
    // Keep your existing regex filter
    new BsonDocument("$match", new BsonDocument(
        "events.event", new BsonRegularExpression(searchString, "i")
    )),
    // Split the events array into individual documents
    new BsonDocument("$unwind", "$events"),
    // Sort by document ID and event date (newest first)
    new BsonDocument("$sort", new BsonDocument
    {
        { "_id", 1 },
        { "events.date", -1 }
    }),
    // Group back into original documents, retaining only the latest event
    new BsonDocument("$group", new BsonDocument
    {
        { "_id", "$_id" },
        { "subject", new BsonDocument("$first", "$subject") }, // Include all top-level fields you need
        { "latestEvent", new BsonDocument("$first", "$events") }
    }),
    // Filter for documents where the latest event is "opened"
    new BsonDocument("$match", new BsonDocument("latestEvent.status", "opened")),
    // Optional: Reconstruct the original document structure
    new BsonDocument("$project", new BsonDocument
    {
        { "_id", 1 },
        { "subject", 1 },
        { "events", new BsonDocument("$push", "$latestEvent") }
    })
};

var results = await collection.Aggregate<Notification>(pipeline).ToListAsync();

Note: In the $group stage, explicitly list all top-level fields from your Notification document that you want to preserve (like subject).

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