I have a database of a the employees of a company that looks like this:

{
    _id: 7698,
    name: 'Blake',
    job: 'manager',
    manager: 7839,
    hired: ISODate("1981-05-01T00:00:00.000Z"),
    salary: 2850,
    department: {name: 'Sales', location: 'Chicago'},
    missions: [
        {company: 'Mac Donald', location: 'Chicago'},
        {company: 'IBM', location: 'Chicago'}
    ]
}

I have an exercise in which I need to write the MongoDb command that returns all them employees who did all their missions in Chicago. I struggle with the all because I cannot find a way to check that all the locations of the missions array are equal to 'Chicago'.

I was thinking about doing it in two time: first find the total number of missions the employee has and then compare it to the number of mission he has in Chicago (that how I would do in SQL I guess). But I cannot found the number of mission the employee did in Chicago. Here is what I tried:

db.employees.aggregate([
    {
        $match: { "missions": { $exists: true } }
    },
    {
        $project: {
            name: 1,
            nbMissionsChicago: {
                $sum: {
                    $cond: [
                        {
                            $eq: [{
                                $getField: {
                                    field: { $literal: "$location" },
                                    input: "$missions"
                                }
                            }, "Chicago"]
                        }, 1, 0
                    ]
                }
            }
        }
    }
])

Here is the result :

{ _id: 7698, name: 'Blake', nbMissionsChicago: 0 }
{ _id: 7782, name: 'Clark', nbMissionsChicago: 0 }
{ _id: 8000, name: 'Smith', nbMissionsChicago: 0 }
{ _id: 7902, name: 'Ford', nbMissionsChicago: 0 }
{ _id: 7499, name: 'Allen', nbMissionsChicago: 0 }
{ _id: 7654, name: 'Martin', nbMissionsChicago: 0 }
{ _id: 7900, name: 'James', nbMissionsChicago: 0 }
{ _id: 7369, name: 'Smith', nbMissionsChicago: 0 }

First of all, is there a better method to check that all the locations of the missions array respect the condition? And why does this commands returns only 0 ?

Thanks!

CodePudding user response：

If all you need is the agents who had all their missions in "Chicago" then you don't need an aggregation pipeline for it, specifically the approach of filtering the array as part of the aggregation can't utilize an index and will make performance even worse.

A simple query should suffice here:

db.collection.find({
  $and: [
    {
      "missions": {
        $exists: true
      }
    },
    {
      "missions.location": {
        $not: {
          $gt: "Chicago"
        }
      }
    },
    {
      "missions.location": {
        $not: {
          $lt: "Chicago"
        }
      }
    }
  ]
})

Mongo Playground

This way we can build an index on the missions field and utilize it properly, any documents with a different value other then "Chigaco" will not match as they will fail the $gt or $lt comparion.

Note that an empty array also matches the condition, you can change the generic "missions" exists condition key into "missions.0": {$exists: true}, this will also require at least one mission.

CodePudding user response：

You are unable to get the correct result as it is not the correct way to iterate the element in an array field.

Instead, you need to work with $size operator to get the size of an array and the $filter operator to filter the document.

Updated: You can directly compare the filtered array with the original array.

db.employees.aggregate([
  {
    $match: {
      "missions": {
        $exists: true
      }
    }
  },
  {
    $project: {
      name: 1,
      nbMissionsChicago: {
        $eq: [
          {
            $filter: {
              input: "$missions",
              cond: {
                $eq: [
                  "$$this.location",
                  "Chicago"
                ]
              }
            }
          },
          "$missions"
        ]
      }
    }
  }
])

Demo @ Mongo Playground