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: "..."}
]
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"
}
}]);
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?
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;
-- ^^^^^^^^^^^^^^^^^^^^^
The $lookup operator is an aggregation operator or an aggregation stage, which is used to join a document from one collection to a document of another collection of the same database based on some queries. Both the collections should belong to the same databases.
For performing MongoDB Join two collections, you must use the $lookup operator. It is defined as a stage that executes a left outer join with another collection and aids in filtering data from joined documents. For example, if a user requires all grades from all students, then the below query can be written: Students.
To access the value of the variable, prefix the variable name with double dollar signs ( $$ ); i.e. "$$<variable>" . If the variable references an object, to access a specific field in the object, use the dot notation; i.e. "$$<variable>.
$expr can build query expressions that compare fields from the same document in a $match stage. If the $match stage is part of a $lookup stage, $expr can compare fields using let variables. See Perform Multiple Joins and a Correlated Subquery with $lookup for an example.
You can use below aggregation
$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"
}}
])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With