Am trying to fetch documents which has exact elements in an array. I already knew the array size so am trying to use $all and $size operator it doesn't work out.

My Documents sample

 {
    "_id" : ObjectId("6395b0d9d35a57df142862dd"),
    "users" : [
        ObjectId("63844feadf507942caaf90e3"),
        ObjectId("638455e5fa983e9cf84c0f3f")
    ],
    "type" : "SINGLE",
    "createdAt" : ISODate("2022-12-11T15:58:41.625 05:30"),
    "updatedAt" : ISODate("2022-12-11T15:58:41.625 05:30"),
    "__v" : 0
},
{
    "_id" : ObjectId("6395b0d9d35a57df142862ce"),
    "users" : [
        ObjectId("63844feadf507942caaf90e3"),
        ObjectId("638455e5fa983e9cf84c0f3f"),
        ObjectId("638455e5fa983e9cf34c0abc"),
    ],
    "type" : "SINGLE",
    "createdAt" : ISODate("2022-12-11T15:58:41.625 05:30"),
    "updatedAt" : ISODate("2022-12-11T15:58:41.625 05:30"),
    "__v" : 0
}

Above is the sample records which i have now i like to fetch the records which has exact users array size and if all the element matches to my array.

Am attaching my query below

db.conversations.find({ users: {$size: 2, $all: [ObjectId("638455e5fa983e9cf84c0f3f") ]}})

the query which i wrote above returns a document, which means my query is wrong i want to return if all the element matches with users array

CodePudding user response：

Maybe like this:

db.collection.find({
$and: [
 {
  users: {
    $size: 2
  }
},
{
  users: {
    $all: [
      ObjectId("638455e5fa983e9cf84c0f3f"),
      ObjectId("63844feadf507942caaf90e3")
    ]
  }
 }
]
})

Playground

CodePudding user response：

In the comments you clarified that this is the behavior that you are looking for:

"i want if all the elements are matching then return me the record."

This means that your current query does not work as it returns this document:

  {
    "_id": ObjectId("6395b0d9d35a57df142862dd")
    "users": [
      ObjectId("63844feadf507942caaf90e3"),
      ObjectId("638455e5fa983e9cf84c0f3f")
    ],
    ...
  }

But that document contains an entry of ObjectId("63844feadf507942caaf90e3") in the users array which you don't want.

One solution here is to use the $allElementsTrue operator. This component of the check would look something like this:

        "$allElementsTrue": [
          {
            "$map": {
              "input": "$users",
              "in": {
                "$in": [
                  "$$this",
                  [
                    ObjectId("638455e5fa983e9cf84c0f3f")
                  ]
                ]
              }
            }
          }
        ]

What the approach above does is map over the users array to make sure that each entry is present in the array provided by the client. Here the list from the $all in the original question is placed as the second argument to $in.

An alternative approach, would be to use the $setDifference operator. That might look as follows:

        $not: {
          $size: {
            "$setDifference": [
              "$users",
              [
                ObjectId("638455e5fa983e9cf84c0f3f")
              ]
            ]
          }
        }
      }

Here we are computing the logical difference between the array in the documents and the array passed into the query. We then check that output to see that the difference is empty (via the negation of the size).

but still its going in agg only right? anything in find query?

What is the requirement for doing this outside of aggregation?

Regardless, these operators are not actually restricted for use solely within the aggregation framework. Via the $expr operator we can use them inside of find() directly.

Therefore the full example for the first approach is as follows (playground example here):

db.conversations.find({
  $expr: {
    "$allElementsTrue": [
      {
        "$map": {
          "input": "$users",
          "in": {
            "$in": [
              "$$this",
              [
                ObjectId("638455e5fa983e9cf84c0f3f")
              ]
            ]
          }
        }
      }
    ]
  },
  users: {
    $size: 2
  }
})

And the second approach (playground example here):

db.conversations.find({
  $expr: {
    $not: {
      $size: {
        "$setDifference": [
          "$users",
          [
            ObjectId("638455e5fa983e9cf84c0f3f")
          ]
        ]
      }
    }
  },
  users: {
    $size: 2
  }
})