如何通过MongoDB Driver for .NET按指定数组排序子文档？

Got it, let's figure out how to sort that Types array inside your Clients subdocuments using the MongoDB Driver for .NET, following your custom order [B,A,D,C]. I'll cover two scenarios: sorting on-the-fly when you query the data, and updating the documents directly in the database to persist the sorted order.

Option 1: Sort at Query Time (No Database Modifications) #

This approach rearranges the Types array when you fetch the document, without changing the data stored in MongoDB. We'll use an aggregation pipeline to add a temporary sort weight to each Type entry, sort by that weight, then clean up the temporary field.

Here's the C# code using the .NET Driver:

var client = new MongoClient("your_connection_string");
var database = client.GetDatabase("your_database_name");
var collection = database.GetCollection<BsonDocument>("your_collection_name");

// Define our custom order mapping: assign lower numbers to items that should come first
var customOrder = new Dictionary<string, int>
{
    { "B", 1 },
    { "A", 2 },
    { "D", 3 },
    { "C", 4 }
};

var aggregationPipeline = new BsonDocument[]
{
    // Optional: target only the specific document you need (remove if sorting all docs)
    new BsonDocument("$match", new BsonDocument("_id", "1033")),
    // Unwind the Clients array to process each client entry individually
    new BsonDocument("$unwind", "$Clients"),
    // Add a temporary sortWeight field to each Type based on our custom order
    new BsonDocument("$addFields", new BsonDocument
    {
        { "Clients.Types", new BsonDocument("$map", new BsonDocument
            {
                { "input", "$Clients.Types" },
                { "as", "type" },
                { "in", new BsonDocument("$mergeObjects", new BsonArray
                    {
                        "$$type",
                        new BsonDocument("sortWeight", new BsonDocument("$switch", new BsonDocument
                            {
                                { "branches", customOrder.Select(kv => new BsonDocument
                                    {
                                        { "case", new BsonDocument("$eq", new BsonArray { "$$type.Class", kv.Key }) },
                                        { "then", kv.Value }
                                    }).ToList()
                                },
                                { "default", 5 } // Push any unmatched Class values to the end
                            }))
                    })
            })
        }
    }),
    // Sort the Types array using the temporary sortWeight field
    new BsonDocument("$addFields", new BsonDocument
    {
        { "Clients.Types", new BsonDocument("$sortArray", new BsonDocument
            {
                { "input", "$Clients.Types" },
                { "sortBy", new BsonDocument("sortWeight", 1) } // 1 = ascending order
            })
        }
    }),
    // Remove the temporary sortWeight field from each Type entry
    new BsonDocument("$addFields", new BsonDocument
    {
        { "Clients.Types", new BsonDocument("$map", new BsonDocument
            {
                { "input", "$Clients.Types" },
                { "as", "type" },
                { "in", new BsonDocument("$unsetField", new BsonDocument
                    {
                        { "field", "$$type" },
                        { "fieldName", "sortWeight" }
                    })
                }
            })
        }
    }),
    // Re-group the Clients entries back into an array
    new BsonDocument("$group", new BsonDocument
    {
        { "_id", "$_id" },
        { "Name", new BsonDocument("$first", "$Name") },
        { "Clients", new BsonDocument("$push", "$Clients") }
    })
};

// Execute the pipeline and get the sorted document
var sortedDocument = collection.Aggregate<BsonDocument>(aggregationPipeline).FirstOrDefault();

Key Notes: #

• We use the $switch operator to map each Class value to a numeric weight, which makes sorting straightforward.
• The $sortArray operator (MongoDB 5.0+) simplifies sorting array elements directly. If you're using an older MongoDB version, you'll need to use $unwind + $sort + $group to reorder the Types array.

Option 2: Update Documents to Persist Sorted Order #

If you want to permanently reorder the Types array in your database, use an update pipeline to modify the document directly:

var updatePipeline = new BsonDocument[]
{
    // Add sortWeight to each Type, sort the array, then merge back into the client entry
    new BsonDocument("$set", new BsonDocument
    {
        { "Clients", new BsonDocument("$map", new BsonDocument
            {
                { "input", "$Clients" },
                { "as", "client" },
                { "in", new BsonDocument("$mergeObjects", new BsonArray
                    {
                        "$$client",
                        new BsonDocument("Types", new BsonDocument("$sortArray", new BsonDocument
                            {
                                { "input", new BsonDocument("$map", new BsonDocument
                                    {
                                        { "input", "$$client.Types" },
                                        { "as", "type" },
                                        { "in", new BsonDocument("$mergeObjects", new BsonArray
                                            {
                                                "$$type",
                                                new BsonDocument("sortWeight", new BsonDocument("$switch", new BsonDocument
                                                    {
                                                        { "branches", customOrder.Select(kv => new BsonDocument
                                                            {
                                                                { "case", new BsonDocument("$eq", new BsonArray { "$$type.Class", kv.Key }) },
                                                                { "then", kv.Value }
                                                            }).ToList()
                                                        },
                                                        { "default", 5 }
                                                    }))
                                            })
                                    })
                                },
                                { "sortBy", new BsonDocument("sortWeight", 1) }
                            }))
                    })
            })
        }
    }),
    // Clean up the temporary sortWeight fields from all Type entries
    new BsonDocument("$set", new BsonDocument
    {
        { "Clients", new BsonDocument("$map", new BsonDocument
            {
                { "input", "$Clients" },
                { "as", "client" },
                { "in", new BsonDocument("$mergeObjects", new BsonArray
                    {
                        "$$client",
                        new BsonDocument("Types", new BsonDocument("$map", new BsonDocument
                            {
                                { "input", "$$client.Types" },
                                { "as", "type" },
                                { "in", new BsonDocument("$unsetField", new BsonDocument
                                    {
                                        { "field", "$$type" },
                                        { "fieldName", "sortWeight" }
                                    })
                                }
                            }))
                    })
            })
        }
    })
};

// Execute the update on the target document
var updateResult = collection.UpdateOne(
    filter: new BsonDocument("_id", "1033"),
    update: updatePipeline
);

Key Notes: #

• This will modify the document in your database, so the sorted order will be there for all future queries.
• If you need to apply this to multiple documents, replace UpdateOne with UpdateMany and adjust the filter accordingly.

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