MongoDB find among subdocuments

Question

I have a bunch of documents where each of them is formatted this way:

{
  "faculty": "Some Faculty",
  "students": [
      {
        "id": "51230867123",
        "age": "21",
        "name": "awdawdawdawawd"
      },
      {
        "id": "0217356102",
        "age": "22",
        "name": "awdawd"
      },
      ...
      ]
}

I've inserted those documents into a single MongoDB collection using insertMany([...]), so now my students table looks like this:

{
  "faculty": "Some Faculty",
  "students": [
      {
        "id": "51230867123",
        "age": "21",
        "name": "John Doe"
      },
      {
        "id": "0217356102",
        "age": "22",
        "name": "Jane Doe"
      },
      ...
      ]
},
{
  "faculty": "Other Faculty",
  "students": [
      {
        "id": "1240876124",
        "age": "21",
        "name": "Jimmy Doe"
      },
      {
        "id": "2309857120578",
        "age": "22",
        "name": "Johnny Doe"
      },
      ...
      ]
},
...

I'd very much like to find out how I can perform a find query on certain subdocuments, specifically students. For example, I'd like to find all students.name with students.age > 23. I've tried many queries, including db.students.find({"students.age": {$gt: /.*B.*/}}, {_id: 0, "students.name": 1}), but such queries always return whole documents for which at least one student fulfills the condition. I'd like to query individual students. Is this possible using find (no aggregate)?

With aggregate, one can use unwrap, which is pretty much exactly what I need to do. I'm however hoping for some tricks to use with find to achieve this behavior.

Answer 1

You can use aggregation operator in projection of find and findOne method, starting from MongoDB 4.2,

• $filter to iterate loop of students array and check required condition, it will return filtered result
• $map to iterate loop of above filtered result and return only name

db.collection.find(
  { "students.age": { $gt: "23" } },
  {
    "students": {
      $map: {
        input: {
          $filter: {
            input: "$students",
            cond: { $gt: ["$$this.age", "23"] }
          }
        },
        in: "$$this.name"
      }
    }
  }
)

Playground