Operator $in with array field

namtv · 2024-08-12T15:27:33.896Z

Hi Everyone!!
I have the following data
Functions collection:

[
  {
    _id: 1,
    function: 'auth_group',
    roles: [ 'superadmin', 'manager' ],
    currentUser: { _id: 2, account: 'admin', roles: [ 'administrator', 'staff' ] }
  },
  {
    _id: 3,
    function: 'auth_user',
    roles: [ 'administrator', 'manager' ],
    currentUser: { _id: 4, account: 'admin', roles: [ 'administrator', 'staff' ] }
  }
]

I want to search for functions where the currentUser has been assigned at least one role. The desired result is:

[
  {
    _id: 3,
    function: 'auth_user',
    roles: [ 'administrator', 'manager' ],
    currentUser: { _id: 4, account: 'admin', roles: [ 'administrator', 'staff' ] }
  }
]

I tried the following method, but the result was not as expected:

db.Functions.aggregate(
  [
    {
      "$match": {
        "$expr": {
          "$in": [
            "$roles",
            "$currentUser.roles"
          ]
        }
      }
    }
  ]
)

Thanks

John_Page · 2024-08-12T15:58:42.493Z

In this cse $in woudl only be true if one of the arrays was an element of the other, what you are looking for in an intersection of the set of values in the arrays - so you need $setIntersection


``var data = [
  {
    _id: 1,
    function: 'auth_group',
    roles: [ 'superadmin', 'manager' ],
    currentUser: { _id: 2, account: 'admin', roles: [ 'administrator', 'staff' ] }
  },
  {
    _id: 3,
    function: 'auth_user',
    roles: [ 'administrator', 'manager' ],
    currentUser: { _id: 4, account: 'admin', roles: [ 'administrator', 'staff' ] }
  }
]

db.b.drop()
db.b.insertMany(data)

var pipeline = [
  {
    "$match": {
      "$expr": 
        { $ne : [ [], 
                  { "$setIntersection": ["$roles","$currentUser.roles"]} 
        ] 
      }
    }
  }

]

db.b.aggregate(pipeline)