How to use $arrayElemAt and remove fields from tha

2020-06-17 06:19发布

问题:

I have 'jobs' and 'users' collection. Every user can create a job for given 'jobCategoryId', that job is then saved in 'jobs' collection and contains 'userId' of it's creator and 'jobCategoryId'.

I'm trying to combine these 2 collections so when I fetch jobs, I'll have 'user' field with all user data instead of 'userId' field for that job. This is how I'm doing it at the moment:

Job.aggregate([{
  $match: {
    jobCategoryId: mongoose.Types.ObjectId(jobCategoryId)
  }
}, {
  $lookup: {
    from: 'users',
    localField: 'userId',
    foreignField: '_id',
    as: 'user'
  }
}, {
  $project: {
    _id: 1,
    description: 1,
    title: 1,
    user: { $arrayElemAt: ['$user', 0] }
  }
}, {
  $project: {
    _id: 1,
    title: 1,
    description: 1,
    'user.name': '$user.name'
  }
}])

This returns following:

[{
  _id: 'some id',
  title: 'title',
  description: 'description',
  user: {
    name: 'Mike'
  }
}]

result is correct but I'm really interested if there is any other way to keep only some fields from user object directly while using $arrayElemAt or am I doomed to use 2 projections in pipeline? Could anyone help me with this little problem? I'd really appreciate it.

回答1:

This is from the syntax of arrayElemAt

The expression can be any valid expression as long as it resolves to an array.

Which means you can construct your array elements however you want. In your case you want only the name. So this should work:

[{
  $match: {
    jobCategoryId: mongoose.Types.ObjectId(jobCategoryId)
  }
}, {  
  $lookup: {  
    from: 'users',
    localField: 'userId',
    foreignField: '_id',
    as: 'user'
  }
}, {  
  $project: {  
    _id: 1,
    description: 1,
    title: 1,
    user: {  
      name: {
        $arrayElemAt: ["$user.name", 0]
      }
    }
  }
}]

Follow up UPDATE: it was asked how to additional properties on top of name . Here is the project:

{  
  $project: {  
    _id: 1,
    description: 1,
    title: 1,
    user: {  
      name: {  
        $arrayElemAt: ["$user.name", 0]
      },
      email: {  
        $arrayElemAt: ["$user.email", 0]
      }
    }
  }
}

Second follow up as Drag0 asked in the comments: If the above isn't good enough because the results generate a user:[] array of size 1 instead of an object user:{} the following can be used.

{  
  $project: {
    _id: 1,
    description: 1,
    title: 1,
    user: {
      $let: {
        vars: {
          firstUser: {
            $arrayElemAt: ["$user", 0]
          }
        },
        in: {
          name: "$$firstUser.name",
          email: "$$firstUser.email"
        }
      }
    }
  }
}