MongoDB 3.6聚合查询：提取数组指定Code对应的Name字段

Got it, let's break this down. Your existing aggregation uses $filter to isolate the English name entry in the Names array, but since $filter returns an array (even if it only has one element), you need an extra step to pull out the Name value directly. Here's the optimized, performance-friendly approach for MongoDB 3.6, plus how to translate this to the .NET Driver.

1. MongoDB Shell Aggregation Query #

We'll extend your existing pipeline to convert the filtered array into a single Name field. Since each document should only have one en entry in Names, we can use $arrayElemAt to grab the first (and only) element from the filtered array, then extract its Name property.

Option 1: Using $map + $arrayElemAt (MongoDB 3.6 compatible) #

This is the most straightforward approach for 3.6:

db.Countries.aggregate([
  {
    $project: {
      _id: 0,
      ID: "$_id",
      Name: {
        $arrayElemAt: [
          // Map the filtered array to just the Name values
          { $map: {
              input: { 
                $filter: { 
                  input: "$Names", 
                  as: "item", 
                  cond: { $eq: ["$$item.Code", "en"] } 
                } 
              },
              as: "filteredItem",
              in: "$$filteredItem.Name"
          }},
          0 // Grab the first (only) Name from the mapped array
        ]
      }
    }
  },
  { $skip: 10 },
  { $limit: 5 }
])

Option 2: Using $let for cleaner readability #

If you prefer more readable code, $let lets you store the filtered element as a variable before extracting the Name:

db.Countries.aggregate([
  {
    $project: {
      _id: 0,
      ID: "$_id",
      Name: {
        $let: {
          vars: {
            englishEntry: {
              $arrayElemAt: [
                { $filter: { input: "$Names", as: "item", cond: { $eq: ["$$item.Code", "en"] } } },
                0
              ]
            }
          },
          in: "$$englishEntry.Name"
        }
      }
    }
  },
  { $skip: 10 },
  { $limit: 5 }
])

Performance Notes #

• Indexing: If you frequently query based on Names.Code, consider adding a multikey index: db.Countries.createIndex({ "Names.Code": 1 }) to speed up the $filter operation.
• Pipeline Order: Keeping $skip and $limit at the end is correct—this reduces the number of documents processed after projection, improving efficiency.
• Handle Missing Entries: If some documents don't have an en entry, add $ifNull to return a default value (e.g., $ifNull: ["$$englishEntry.Name", "N/A"] in the $let option).

2. .NET Driver Implementation #

You can replicate this logic using either BsonDocument syntax or strong-typed expressions (preferred for maintainability).

Strong-Typed Approach (Recommended) #

First, define your DTO to match the desired output:

using MongoDB.Bson;
using MongoDB.Driver;

public class CountryDto
{
    public ObjectId ID { get; set; }
    public string Name { get; set; }
}

// Assume you have a Country model matching your collection structure
public class Country
{
    public ObjectId Id { get; set; }
    public List<CountryName> Names { get; set; }
    // Other fields...
}

public class CountryName
{
    public string Code { get; set; }
    public string Name { get; set; }
}

Then build the aggregation pipeline with LINQ expressions (the driver translates this to the MongoDB operators automatically):

var mongoClient = new MongoClient("your-connection-string");
var database = mongoClient.GetDatabase("your-db-name");
var collection = database.GetCollection<Country>("Countries");

// Build the pipeline stages
var projectStage = PipelineStageDefinitionBuilder.Project<Country, CountryDto>(
    country => new CountryDto
    {
        ID = country.Id,
        Name = country.Names
            .Where(n => n.Code == "en")
            .Select(n => n.Name)
            .FirstOrDefault()
    }
);

var skipStage = PipelineStageDefinitionBuilder.Skip<CountryDto>(10);
var limitStage = PipelineStageDefinitionBuilder.Limit<CountryDto>(5);

// Combine stages and execute
var pipeline = new PipelineDefinition<Country, CountryDto>(new[] { projectStage, skipStage, limitStage });
var results = await collection.AggregateAsync(pipeline);
var countryList = await results.ToListAsync();

BsonDocument Approach (For Fine-Grained Control) #

If you need to match the shell query exactly, use BsonDocument syntax:

var projectStage = PipelineStageDefinitionBuilder.Project<BsonDocument, BsonDocument>(
    new BsonDocument
    {
        { "_id", 0 },
        { "ID", "$_id" },
        {
            "Name",
            new BsonDocument("$arrayElemAt", new BsonArray
            {
                new BsonDocument("$map", new BsonDocument
                {
                    { "input", new BsonDocument("$filter", new BsonDocument
                        {
                            { "input", "$Names" },
                            { "as", "item" },
                            { "cond", new BsonDocument("$eq", new BsonArray { "$$item.Code", "en" }) }
                        })
                    },
                    { "as", "filteredItem" },
                    { "in", "$$filteredItem.Name" }
                }),
                0
            })
        }
    }
);

var skipStage = PipelineStageDefinitionBuilder.Skip<BsonDocument>(10);
var limitStage = PipelineStageDefinitionBuilder.Limit<BsonDocument>(5);

var pipeline = new PipelineDefinition<BsonDocument, BsonDocument>(new[] { projectStage, skipStage, limitStage });
var results = await collection.AggregateAsync(pipeline);
var documents = await results.ToListAsync();

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