How to project single array field

Question

starting from this document schema

{ 
    "_id" : ObjectId("5a5cfde58c8a4a35a89726b4"), 
    "Names" : [ 
                { "Code" : "en", "Name" : "ITALY" }, 
                { "Code" : "it", "Name" : "ITALIA" } 
              ],
    "TenantID" : ObjectId("5a5cfde58c8a4a35a89726b2"),
    ...extra irrelevant fields
}

I need to get an object like this

{ 
    "ID" : ObjectId("5a5cfde58c8a4a35a89726b4"), 
    "Name" : "ITALY"
}

Filtering by array's Code field (in the sample by 'en').

I wrote an aggregate query, this

db.Countries.aggregate([{$project: {_id:0, 'ID': '$_id', 'Names': {$filter: {input: '$Names', as: 'item', cond: {$eq: ['$$item.Code', 'en']}}}}},{$skip: 10},{$limit: 5}]);

that correctly return only documents with 'en' Names values, returning only the sub array matching elements.

Now I cannot find the way to return only the Name field value into a new 'Name' field. Which is the best and most performing method to do this ?

Actually I'm trying on Mongo shell, but later I need to replicate this behavior with .NET driver.

Using MongoDB 3.6

Thanks

Answer 1

You can try below aggregation query.

$match to only consider documents where there is atleast one element in array matching input criteria.

$filter array on the input criteria and $arrayElemAt to project the single matched element.

$let to output name from matched object.

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