Beginner here. Give me a find() query that will give age of "Sam"

Need to query the contractors array of documents. "name" field is a document that has fname: "Sam". But the "age" field is not in this "name" document, its in the preceding level doc.

Need a extensive query, that will work even if the position of "Sam" be any random position in a large DB

expected O/P: {age : 22}

{
    "name" : "Apples",
    "qty" : 120,
    "from" : [
        "USA",
        "Brazil",
        "Russia"
    ],
    "contractors" : [
        {
            "name" : {
                "fname" : "Sam",
                "lname" : "McGregor"
            },
            "age" : 22
        },
        {
            "name" : {
                "fname" : "zxc",
                "lname" : "asd"
            },
            "age" : 32
        }
    ]
}

CodePudding user response：

Query1

• find the member of contractors with fname=sam and lname= McGregor (if you want only the name, remove the name filter)
• and project to keep this matching members age
• use findOne method of your driver, because age will be the same in all anyways

Test code here

find({
  "contractors": {
    $elemMatch: {
      "name.fname": "Sam",
      "name.lname": "McGregor"
    }
  }
},
{
  "contractors.age.$": 1,
  "_id": 0
})

Query2

• aggregation solution
• match the fname,lfname (needs multikey index to be fast)
• limit 1 because age will be the same anyways
• filter to find from the array the Sam
• project to keep only his age

*the difference from the above is that element match, saves the member that matched in $ so we don't have to re-search for it. But this solution will be fast if you have the mulitikey index.

Test code here

aggregate(
[{"$match":  
    {"$and": 
      [{"contractors.name.fname": {"$eq": "Sam"}},
        {"contractors.name.lname": {"$eq": "McGregor"}}]}},
  {"$limit": 1},
  {"$project": 
    {"_id": 0,
      "age": 
      {"$getField": 
        {"field": "age",
          "input": 
          {"$first": 
            {"$filter": 
              {"input": "$contractors",
                "cond": 
                {"$and": 
                  [{"$eq": ["$$this.name.fname", "Sam"]},
                    {"$eq": ["$$this.name.lname", "McGregor"]}]}}}}}}}}])