Stack Exchange
Filter in Mongo DB with $in
I cannot filter items that only have a specific role:
When I try to filter only the element with rol ROL_CONTADOR return all elements of items, but the second element in items, not should filter.
 db.menu.find({ "items": { "$elemMatch": { "roles": {$in: ["ROL_ARTICULOS"] } } } })

{
        "label": "Reportes",
        "icon": "list",
        "items": [
            {
                "label": "Cajas",
                "link": "/articulos",
                "icon": "check_circle_outline",
                "roles": [
                    "SYSADMIN",
                    "ROL_CONTADOR"
                ]
            },
            {
                "label": "Ventas",
                "link": "/articulos",
                "icon": "check_circle_outline",
                "roles": [
                    "SYSADMIN",
                    "ROL_VENTAS"
                ]
            }
        ]
    },
    {
        "label": "Facturas",
        "icon": "list",
        "items": [
            {
                "label": "Administración",
                "link": "/articulos",
                "icon": "check_circle_outline",
                "roles": [
                    "SYSADMIN",
                    "ROL_FACTURAS"
                ]
            }
        ]
    }

I have to use the $in clause because a user can have multiple roles, but I need only to get the items that fulfill the role

So are you looking to do this with a C# query, or are the C# and webapi tags red herrings?

what do you mean about not should filter ? its a looping and must be filtered. did you mean to filter the output as well or would you like to post your expectation result to help understand the question

db.collection.aggregate([ { $match: { "items.roles": { $in: [ "ROL_CONTADOR", "ROL_FACTURAS", "ROL_VENTAS"] } } }, { $set: { items: { $filter: { input: "$items", cond: { $or: [ { $in: [ "ROL_CONTADOR", "$$this.roles"] }, { $in: [ "ROL_FACTURAS", "$$this.roles"] }, { $in: [ "ROL_VENTAS", "$$this.roles"] }] } } } } }])


As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Try this one:
db.collection.aggregate([
   { $match: { "items.roles": "ROL_CONTADOR" } },
   {
      $set: {
         items: {
            $filter: {
               input: "$items",
               cond: { $in: ["ROL_CONTADOR", "$$this.roles"] }
            }
         }
      }
   }
])

Mongo Playground

I add $in but items always return empty: db.collection.aggregate([
{
$match: {
"items.roles": {
$in: [
"ROL_CONTADOR",
"ROL_FACTURAS"
]
}
}
},
{
$set: {
items: {
$filter: {
input: "$items",
cond: {
$in: [
[
"ROL_CONTADOR",
"ROL_FACTURAS"
],
"$$this.roles"
]
}
}
}
}
}
])

The $in operator checks if a value ROL_CONTADOR is within the array.  If you like to check for multiple values, use $setIsSubset
