Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

$nin with the $expr

I have a query to find if a user CreatedBy is in a SharedWith. I want to inverse the query to check if the CreatedBy is not in SharedWith.

[
    {
        "$match": {
            "$and": [
                {
                    "$and": [
                        {
                            "SharedWith": {
                                "$exists": true
                            }
                        },
                        {
                            "$expr": {
                                "$in": [
                                    "$CreatedBy",
                                    "$Multi_User"
                                ]
                            }
                        }
                    ]
                }
            ]
        }
    }
]

MongoDB does not support a direct $nin or $not for $and query.

Any idea how to achieve this.

The user document looks like this,

   Collection = [
        {"CreatedBy":
             {"_id": "User001",
              "Email": "[email protected]",
              "Name": "User001"},
         "SharedWith": [
             {"_id": "User001",
              "Email": "[email protected]",
              "Name": "User001"},
             {"_id": "User002",
              "Email": "[email protected]",
              "Name": "User002"},
             {"_id": "User003",
              "Email": "[email protected]",
              "Name": "User003"},
         ]}

    ]
like image 552
Bhavani Ravi Avatar asked Dec 03 '18 13:12

Bhavani Ravi


People also ask

What is $Nin in MongoDB?

$nin selects the documents where: the field value is not in the specified array or. the field does not exist.

Which operator is used Combine 2 expressions in MongoDB?

Logical Operators Joins two or more queries with a logical AND and returns the documents that match all the conditions. Join two or more queries with a logical OR and return the documents that match either query. The opposite of the OR operator.

How do I use $in in MongoDB?

Use the $in Operator to Match Values This query selects all documents in the inventory collection where the value of the quantity field is either 5 or 15. Although you can write this query using the $or operator, use the $in operator rather than the $or operator when performing equality checks on the same field.

How write not query in MongoDB?

MongoDB provides different types of comparison query operators and $nin (not in) operator is one of them. This operator is used to select those documents where the value of the field is not equal to any of the given value in the array and the field that does not exist.


1 Answers

$nin is an query operator not an aggregation operator and also $expr only supports the aggregation operators not the query ones. So, You should probably use $not $in with the $expr expression in this manner

{
  "$match": {
    "$and": [
      {
        "$or": [
          {
            "Multi_User": {
              "$exists": False
            }
          },
          {
            "$expr": {
              "$not": { "$in": ["$CreatedBy", "$Multi_User"] }
            }
          }
        ]
      }
    ]
  }
}
like image 121
Ashh Avatar answered Oct 05 '22 09:10

Ashh