MongoDB聚合：按ObjectId将_id作为字符串的数组筛选

Question

我有以下收藏:

const movieSchema = new Schema({
  title: String
  ...
})

const userSchema = new Schema({
  firstName: String,
  lastName: String,
  movies: [
    movie: {
      type: Schema.Types.ObjectId,
      ref: 'Movie'
    },
    status: String,
    feeling: String
  ]
  ...
})

我试图将电影（及其所有细节）与用户状态和对该电影的感觉相匹配，并使用聚合：

Movie.aggregate([
  { $match: { _id: ObjectId(movieId) } },
  {
    $lookup: {
      from: 'users',
      as: 'user_status',
      pipeline: [
        { $match: { _id: ObjectId(userId) } },
        {
          $project: {
            _id: 0,
            movies: 1
          }
        },
        { $unwind: '$movies' }
      ]
    }
  },
  
])

其中返回：

[
  {
    _id: 610b678702500b0646925542,
    title: 'The Shawshank Redemption',
    user_status: [
      {
        "movies": {
          "_id": "610b678702500b0646925542",
          "status": "watched",
          "feeling": "love"
        }
      },
      {
        "movies": {
          "_id": "610b678502500b0646923627",
          "status": "watched",
          "feeling": "like"
        }
      },
      {
        "movies": {
          "_id": "610b678502500b0646923637",
          "status": "watched",
          "feeling": "like"
        }
      },
    ]
  }
]

我希望的结果是匹配user_status中的第一部电影，以获得最终结果：

[
  {
    _id: 610b678702500b0646925542,
    title: 'The Shawshank Redemption',
    status: "watched",
    feeling: "love"
  }
]

我想我的下一步是:

{
  $addFields: {
    user_status: {
      $filter: {
        input: '$user_status',
        cond: {
          $eq: ['$$this.movies._id', '$_id']
        }
      }
    }
  }
}

但是它不起作用——不确定这个$addFields是否正确，我知道的一个问题是，我的第一个_id是一个ObjectId，第二个似乎是一个字符串。

Answer 1

如果我理解正确，你可以在已经存在的$look kup管道中$Filter用户，这会让后面的事情变得更简单：

db.movies.aggregate([
  {$match: {_id: ObjectId(movieId)}},
  {
    $lookup: {
      from: "users",
      as: "user_status",
      pipeline: [
        {$match: {_id: ObjectId(userId)}},
        {$project: {
            movies: {
              $first: {
                $filter: {
                  input: "$movies",
                  cond: {$eq: ["$$this.movie", ObjectId(movieId)]}
                }
              }
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      title: 1,
      feeling: {$first: "$user_status.movies.feeling"},
      status: {$first: "$user_status.movies.status"}
    }
  }
])

看看它是如何在操场上工作的例子