MongoDB中的条件$查找? [英] 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 很陌生;我刚刚想出了如何使用 $lookup
聚合阶段:
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;
-- ^^^^^^^^^^^^^^^^^^^^^
推荐答案
你可以使用下面的聚合
$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中的条件$查找?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!