database - Mongodb search in Document and subDocument through single query

Question

I have a document with an array sub-document

I need to search on document and sub-document through a single query

I need results only of the sub-document array data, passing the criteria

Sample Data [2 documents]

{
  _id : ObjectId("512e28984815cbfcb21646a7"),
  name: David,
  list: [
    {
      sport: basketball,
      score: 100
    },
    {
      sport: cricket,
      score: 30
    }
    {
      sport: rugby,
      score: 100
    }
    ]
},

{
  _id : ObjectId("879e28664815cbfcb21622g9"),
  name: Shawn,
  list: [
    {
      sport: basketball,
      score: 100
    },
    {
      sport: cricket,
      score: 50
    }
    {
      sport: rugby,
      score: 20
    }
    ]
}

Expected Result

List of games in which David's score is 100

• Document query name = David
• Sub-document query score = 100

Response: [basketball, rugby]

---

Query tried but getting NULL is result

findOne({name:'David', "list.score": 100})

Answer 1

You can use an aggregation pipeline.

• For name use a simple $match
• For the array use the $filter function

Would be this one:

db.collection.aggregate([
  { $match: { name: "David" } },
  {
    $set: {
      list: {
        $filter: {
          input: "$list",
          cond: {$eq: ["$$this.score", 100] }
        }
      }
    }
  }
])