CODEWITHSUNDEEP
mongodbmongodb-query
Manuel Jordan
Manuel Jordan

Reputation: 16271

Arrays working $elemMatch for $or and $and scenarios

Assuming exists the following data for simplicity.

[
    {
        "special_colors": ["Blue", "Black", "Red"],
    },
    {
        "special_colors": ["Blue", "Green", "Red"],
    },
    {
        "special_colors": ["Black", "Blue", "Red"],
    }
]

If I execute:

db.fighters.find({"special_colors.2": "Red"}, {"_id": 0}).pretty()

It works and returns all documents.

If I execute:

db.fighters.find({"special_colors.0": "Blue"}, {"_id": 0}).pretty()

It works and returns the first and second documents.

If I execute:

db.fighters.find({"special_colors.1" : "Blue"}, {"_id" : 0}).pretty()

It works and returns the third document.

To retrieve all the documents without matter in what position (index) is located either Blue or Black, I do the following and works fine:

db.fighters.find({"special_colors" : {$elemMatch: {$eq: "Blue"} } }, {"_id" : 0}).pretty()

Situation/Scenario

I want apply a OR, it to retrieve all the documents where special_colors has either Blue or Black (without matter the position of index), I tried many variations such as:

db.fighters.find({"special_colors" : {$elemMatch: {$eq: "Blue", $eq: "Black"} } }, {"_id" : 0}).pretty()

when is executed returns some documents but only considered the second part (Black) and the first part (Blue) was totally ignored

With

db.fighters.find({"special_colors" : {$elemMatch: {$eq: ["Blue", "Black"]} } }, {"_id" : 0, }).pretty()

when is executed returns nothing

With

db.fighters.find({"special_colors" : {$elemMatch: {$or: ["Blue", "Black"] } } }, {"_id" : 0}).pretty()

when is executed arises the following error message: $or/$and/$nor entries need to be full objects

and with:

db.fighters.find({
    "special_colors": {
        $or: [{$elemMatch: {$eq: "Blue"}},
            {$elemMatch: {$eq: "Black"}}
        ]
    }
}, {"_id": 0}).pretty()

when is executed arises the following error message: unknown operator: $or

So what could be the correct syntax for OR and AND?

Therefore:

Upvotes: 1

Views: 107

Answers (2)

turivishal
turivishal

Reputation: 36104

Retrieve all the documents where special_colors has either Blue or Black (without matter the position of index)

$in:

  • Selects the documents where the value of a field equals any value in the specified array
db.fighters.find({
  special_colors: { $in: ["Blue", "Black"] }
},
{"_id" : 0}).pretty()

Playground

$or: (equivelant to $in)

  • don't need to use $elemMatch for array of string
db.fighters.find({
  $or: [
    { special_colors: "Blue" },
    { special_colors: "Black" }
  ]
},
{"_id" : 0}).pretty()

Playground

Retrieve all the documents where special_colors has both Blue and Black (without matter the position of index)

$all:

  • Equivalent to an $and operation of the specified values
db.fighters.find({
  special_colors: {
    $all: ["Blue", "Black"]
  }
},
{"_id" : 0}).pretty()

Playground

$and: (equivelant to $all)

db.fighters.find({
  $and: [
    { special_colors: "Blue" },
    { special_colors: "Black" }
  ]
},
{"_id" : 0}).pretty()

Playground

Upvotes: 2

Tom Slabbaert
Tom Slabbaert

Reputation: 22276

While in theory you can use $elemMatch for this it's more suited to be used for nested documents within the array. Because Mongo flattens all arrays for the purpose of queries you can just use $in for your "or" condition and $all for your "and" condition, like so:

db.collection.find({
  special_colors: {
    $in: [
      "Blue",
      "Black"
    ]
  }
})

Will match any document that has blue or black.

Mongo Playground

And:

db.collection.find({
  special_colors: {
    $all: [
      "Blue",
      "Black"
    ]
  }
})

Will match documents that have both colors in their array.

Mongo Playground

Upvotes: 1

Related Questions