How to search in array of object and get only matching object from the array in MongoDB-CodePudding

Suppose I have this data:

[
  {
    "_id": 101,
    "name": "ABC",
    "students": [
      {
        "name": "john",
        "age": 10,
        "city": "CA"
      },
      {
        "name": "danial",
        "age": 15,
        "city": "KA"
      }
    ]
  },
  {
    "_id": 102,
    "name": "DEF",
    "students": [
      {
        "name": "adam",
        "age": 20,
        "city": "NY"
      },
      {
        "name": "johnson",
        "age": 12,
        "city": "CA"
      }
    ]
  }
]

Now I want to fetch the name and the students whose city is CA.

What I have done is:

db.collection.find({
  "students.city": "CA"
},
{
  students: {
    $elemMatch: {
      "city": "CA"
    }
  }
})

and it returns:

[
  {
    "_id": 101,
    "students": [
      {
        "age": 10,
        "city": "CA",
        "name": "john"
      }
    ]
  },
  {
    "_id": 102,
    "students": [
      {
        "age": 12,
        "city": "CA",
        "name": "johnson"
      }
    ]
  }
]

But the name is missing i.e. ABC and DEF, how to include that?

Now If I do something like this:

db.collection.find({
  students: {
    $elemMatch: {
      city: "CA"
    }
  }
})

Then it is returning name and as well as students array but it is included those students whose city is not CA.

[
  {
    "_id": 101,
    "name": "ABC",
    "students": [
      {
        "age": 10,
        "city": "CA",
        "name": "john"
      },
      {
        "age": 15,
        "city": "KA",
        "name": "danial"
      }
    ]
  },
  {
    "_id": 102,
    "name": "DEF",
    "students": [
      {
        "age": 20,
        "city": "NY",
        "name": "adam"
      },
      {
        "age": 12,
        "city": "CA",
        "name": "johnson"
      }
    ]
  }
]

Here is the link to the playground https://mongoplayground.net/p/NHHu3GahKoK

CodePudding user response：

You need to add name: 1 into the projection.

db.collection.find({
  "students.city": "CA"
},
{
  name: 1,
  students: {
    $elemMatch: {
      "city": "CA"
    }
  }
})

Sample Mongo Playground