查询依赖于 mongodb 中其他文档值的文档

2022-01-13 00:00:00 mongodb nosql javascript mongoose

想象下面的猫鼬模型:

const UserSchema = Schema({
  //_id: ObjectId,
  //more fields,
  blockedIds: [{
    type: ObjectId,
    ref: 'User'
  }]
})

获取与某个 _id 的用户的阻塞 ID 不匹配的所有用户的最有效方法是什么?

What is the most efficient way to get all users that don't match the blockedIds of an user with a certain _id?

一种天真的方法是执行两个查询:

A naive way would be to to perform two queries: 

User.findById(id).then(user => {
  return User.find({_id: {$nin: user.blockedIds}})
})

是否可以使用聚合框架和 $facets 在一个查询中完成?

Is it possible to use the aggregation framework and $facets to accomplish that in one query?

推荐答案

试试 non-correlated 来自 3.6 的子查询，用于您的用例.

Try non-correlated sub query from 3.6 for your use case.

有点像

User.aggregate(
 [{$lookup:{
   from: "users",
   pipeline:[
    {$match: {_id:mongoose.Types.ObjectId(id)}},
    {$project: {_id:0,blockedIds:1}}
   ],
   as: "noncr"
 }},
 {$match:{
   $expr:{
     $not:[
      {$in:[
        $_id,
        {$arrayElemAt:["$noncr.blockedIds",0]}
      ]}
    ]
  }
}},
{$project:{noncr:0}}]
)

$lookup 为输入 ID 拉入blockedIds"，然后是 $match 过滤_id"不在blockedIds列表中的文档.

$lookup to pull in the "blockedIds" for input id followed by $match to filter the documents where "_id" is not in list of blockedIds.

$expr 允许在 $match 阶段使用聚合比较运算符.

$expr allows use of aggregation comparison operators in $match stage.

$arrayElemAt 从 $lookup 数组中获取第一个元素.

$arrayElemAt to fetch the first element from $lookup array.

$in 将_id 与blockedIds 进行比较.

$in to compare the _id against blockedIds.

$project 排除以从最终响应中删除noncr"字段.

$project with exclusion to remove the "noncr" field from the final response.

请注意，当您测试查询时，在查找阶段的from"属性中使用集合名称而不是模型或模式名称.

Please note when you test query use the collection name not model or schema name in "from" attribute of look up stage.

相关文章