Here is a collection in MongoDB that contains documents with the following structure:
{
"_id": {
"$oid": "663bde27b35e766556ebc60c"
},
"email": "diego.latorre@guane.com.co",
"name": "juan",
"accessControl": [
{
"module": "seguimiento",
"organization": {
"$oid": "64591e18c826eb8dec7cff39"
},
"role": {
"_id": {
"$oid": "64591e18c826eb8dec7cff3c"
},
"name": "owner"
}
},
{
"module": "observatorio",
"organization": null,
"role": {
"name": "administrator",
"id": {
"$oid": "663e3814b35e766556ebc623"
}
}
}
],
"emailVerified": false,
"disabled": false
}
(in access control there may be several modules that correspond to different organizations) I need to make a query to bring all the users belonging to an organization and group them by role (i.e. I receive the organization id as input). So, I wrote the following aggregation:
[
{
$match: {
"accessControl.organization": ObjectId(
"64591e18c826eb8dec7cff39"
),
},
},
{
$group: {
_id: {
role: "$accessControl.role.name",
id: "$accessControl.role.id",
},
users: {
$push: {
_id: "$_id",
disabled: "$disabled",
email: "$email",
emailVerified: "$emailVerified",
name: "$name",
accessControl: ["$accessControl"],
},
},
},
},
{
$project: {
_id: 0,
role: "$_id",
users: 1,
},
},
]
My aggregation ALMOST meets the objective, but has the problem that since there are users with more than one module in access control, my result is like this:
{
"users": [
{
"_id": {
"$oid": "663bde27b35e766556ebc60c"
},
"disabled": false,
"email": "diego.latorre@guane.com.co",
"emailVerified": false,
"name": "juan",
"accessControl": [
[
{
"module": "seguimiento",
"organization": {
"$oid": "64591e18c826eb8dec7cff39"
},
"role": {
"_id": {
"$oid": "64591e18c826eb8dec7cff3c"
},
"name": "owner"
}
},
{
"module": "observatorio",
"organization": null,
"role": {
"name": "administrator",
"id": {
"$oid": "663e3814b35e766556ebc623"
}
}
}
]
]
}
],
"role": {
"role": [
"owner",
"administrator"
],
"id": [
{
"$oid": "663e3814b35e766556ebc623"
}
]
}
}
As you can see, in the role section, both "owner" and "administrator" were brought in, but I only need owner to appear, since it is the module in which the organization_id matches. If I place a first stage with this:
{
$unwind: "$accessControl",
},
I would get my desired result, but I will have many users so it is not very efficient to unwind. What can I do to be able to obtain only the role of the match match?
As recommended in the comments, one approach could be to "override" accessControl with a stage of $addFields:
db.collection.aggregate([
{
$match: {
"accessControl.organization": ObjectId("64591e18c826eb8dec7cff39")
}
},
{
$addFields: {
"accessControl": {
$filter: {
input: "$accessControl",
as: "access",
cond: {
$eq: [
"$$access.organization",
ObjectId("64591e18c826eb8dec7cff39")
]
}
}
}
}
},
{
$group: {
_id: {
role: "$accessControl.role.name",
id: "$accessControl.role.id"
},
users: {
$push: {
_id: "$_id",
disabled: "$disabled",
email: "$email",
emailVerified: "$emailVerified",
name: "$name",
accessControl: [
"$accessControl"
]
}
}
}
},
{
$project: {
_id: 0,
role: "$_id",
users: 1
}
}
])
I did performance tests and at least for my case, this approach is considerably better than unwind