MongoDB $and query

user_8059459 0
Viewed 57

I want a query in MongoDB that does the following:

  • A document returned profession.organization property can be the string 'bank'.
  • A document returned profession.city property can be the string 'NY'.
  • However, when the document has 'bank' for 'profession.organization' AND 'NY' for 'profession.city' the document must be excluded.

To sum up a document can have city 'NY' or organization 'bank' but must be excluded if it has these properties at the same time.

What I have tried so far.

    targetObj = await db.db(MDBC.db).collection(MDBC.pC)
        .aggregate([{
            $match: {
                $and: [
                { 'profession.organization': { $ne: 'bank' } }, 
                { 'profession.city': { $ne: 'NY' } }
              ]
            }
        }, { $sample: { size: 1 } }]).next();
mongodb-query mongodb javascript
3 Answers

Try this one:

targetObj = await db.db(MDBC.db).collection(MDBC.pC)
 .aggregate([
  {
    $match: {
      $and: [
        {
          $or: [
            { "profession.organization": "bank" },
            { "profession.city": "NY" }
          ]
        },
        {
          $or: [
            {
              "profession.organization": "bank",
              "profession.city": { $ne: "NY" }
            },
            {
              "profession.organization": { $ne: "bank" },
              "profession.city": "NY"
            }
          ]
        }
      ]
    }
  },
  { $sample: {size: 1} } ]).next();

MongoPlayground

user_37104900

I think the query which is used in the accepted answer could be much shorter.

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          "profession.organization": "bank",
          "profession.city": {
            $ne: "NY"
          }
        },
        {
          "profession.organization": {
            $ne: "bank"
          },
          "profession.city": "NY"
        }
      ]
    }
  },
  {
    $sample: {
      size: 1
    }
  }
])

and one more thing I want to say is if you also want to include documents that don't have either of those two properties then you should use this:

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          "profession.organization": "bank",
          "profession.city": {
            $ne: "NY"
          }
        },
        {
          "profession.organization": {
            $ne: "bank"
          },
          "profession.city": "NY"
        },
        {
          "profession.organization": {
            $ne: "bank"
          },
          "profession.city": {
            $ne: "NY"
          }
        }
      ]
    }
  },
  {
    $sample: {
      size: 1
    }
  }
])

this will also include documents like-

  {
    "profession": {
      "organization": "someBank",
      "city": "notNA"
    }
  }
user_97651670

Unfortunately, at the time of writing, there is no matching operator in MongoDB to help out with this. But you can achieve a similar result by combining the $or and $ne operators. Try something like this for your query:

const query = {
  $or: [
    {
      // Include all non-banks outside of NY.
      'profession.city': { $ne: 'NY' },
      'profession.organization': { $ne: 'bank' }
    },
    {
      // Include all non-banks in NY.
      'profession.city': 'NY',
      'profession.organization': { $ne: 'bank' }
    },
    {
      // Include all banks outside of NY.
      'profession.city': { $ne: 'NY' },
      'profession.organization': 'bank'
    }
  ]
};
user_5905220
Related