在MongoDB中有条件的$ lookup? [英] Conditional $lookup in MongoDB?

问题描述

我在MongoDB 3.6中有两个集合:

I have two collections in MongoDB 3.6:

users: [
  {name: "John", allowedRoles: [1, 2, 3]},
  {name: "Charles", allowedRoles: [1]},
  {name: "Sarah", isAdmin: true}
]

roles: [
  {_id: 1, name: "Foo", description: "This role allows to foo the blargs"},
  {_id: 2, name: "Bar", description: "..."},
  {_id: 3, name: "Doh", descripcion: "..."}
]

我是MongoDB的新手.我只是想出了如何使用

I'm very new to MongoDB; I just figured out how to query an user and join all the data from his roles, using the $lookup aggregation stage:

db.users.aggregate([{
  "$match": { "name": "John" }          // Or without this $match part, to get all users
},{                                     //
  "$lookup": {
    "from": "roles",
    "localField": "allowedRoles",
    "foreignField": "_id",
    "as": "roles"
  }
}]);

它适用于我的普通用户，这些用户具有一系列允许的角色ID.我还有管理员用户，可以访问所有现有角色，但没有allowedRoles数组(这将是一个负担，因为会频繁创建新角色).因此，我没有指定连接字段，而是使用空管道执行了$lookup来获取两个集合的笛卡尔积:

It works for my regular users, who have an array of allowed roles IDs. I have also administrator users, which can access all existing roles, but don't have the allowedRoles array (it would be a burden to maintain, since new roles will be created frequently). So, instead of specifying the join fields, I do a $lookup with an empty pipeline, to get the cartesian product of both collections:

db.users.aggregate([{
  "$match": { "name": "Sarah" }
},{
  "$lookup": {
    "from": "roles",
    "pipeline": [],
    "as": "roles"
  }
}]);

通过单个查询，是否可以同时拥有这两个条件?使用条件表达式或其他东西吗?

Is there any way to have both, with a single query? With a conditional expression or something?

在SQL数据库中，我将简单地在连接中包括条件:

In an SQL database, I would simply include the condition in the join:

select users.*, roles.*
from users
left join users_x_roles inter on users.id = inter.user_id
left join roles on inter.role_id = roles.id or users.is_admin = 1;
--                                          ^^^^^^^^^^^^^^^^^^^^^

推荐答案

您可以在下面的聚合中使用

You can use below aggregation

$expr 允许您可以在其中使用聚合运算符.因此，您可以轻松使用 $cond 汇总具有allowedRoles而没有

$expr allows you use aggregation operator inside it. So you can easily use $cond aggregation for the users who has allowedRoles and who hasn't

db.users.aggregate([
  { "$match": { "name": "Charles" }},
  { "$lookup": {
    "from": "roles",
    "let": { "ar": "$allowedRoles" },
    "pipeline": [
      { "$match": {
        "$expr": {
          "$cond": [
            { "$eq": [{ "$type": "$$ar" }, "missing"] },
            {},
            { "$in": ["$_id", "$$ar"] }
          ]
        }
      }}
    ],
    "as": "roles"
  }}
])

这篇关于在MongoDB中有条件的$ lookup?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！