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?

CodePudding user response：

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