How to make and requests in mongodb queries

I’ve worked on this for about an hour now and I can’t figure anything out that works so sorry if this is obvious.

I want my query to only return results where every result matches, but right now it returns a result if at least one match is found.

My document looks like this…

{
    country: 'Great Britain',
    data: [
        {
            school: 'King Alberts',
            staff: [
                {
                    name: 'John',
                    age: 37,
                    position: 'Head Teacher'
                },
                {
                    name: 'Grace',
                    age: 63,
                    position: 'Retired'
                },
                {
                    name: 'Bob',
                    age: 25,
                    position: 'Receptionist'
                }
            ]
        },
        {
            school: 'St Johns',
            staff: [
                {
                    name: 'Alex',
                    age: 22,
                    position: 'Head of Drama'
                },
                {
                    name: 'Grace',
                    age: 51,
                    position: 'English Teacher'
                },
                {
                    name: 'Jack',
                    age: 33,
                    position: 'Receptionist'
                }
            ]
        },
        // ... more schools
    ]
}

The query I’m currently making looks like…

{ 'data.staff.name': { $in: names } }

and the ‘names’ array that is being provided looks like [‘Bob’, ‘Grace’, ‘John’, ‘Will’, ‘Tom’]. Currently both schools are being returned when I make this query, I think it’s because the ‘names’ array contains ‘Grace’ which is a name present at both schools and so the document it matching. Does anyone know if there’s a query I could make so mongodb only returns the school object if every name in the ‘names’ array is a member of staff at the school?

>Solution :

You need to use the aggregation pipeline for this, after matching the document we’ll just filter out the none matching arrays, like so:

db.collection.aggregate([
  {
    $match: {
      "data.staff.name": {
        $in: names
      }
    }
  },
  {
    $addFields: {
      data: {
        $filter: {
          input: "$data",
          cond: {
            $eq: [
              {
                $size: {
                  "$setIntersection": [
                    "$$this.staff.name",
                    names
                  ]
                }
              },
              {
                $size: "$$this.staff"
              }
            ]
          }
        }
      }
    }
  }
])

Mongo Playground