問題描述
用於 $or 和 $and 場景的 $elemMatch 數組 (Arrays working $elemMatch for $or and $and scenarios)
為簡單起見,假設存在以下數據。
[
{
"special_colors": ["Blue", "Black", "Red"],
},
{
"special_colors": ["Blue", "Green", "Red"],
},
{
"special_colors": ["Black", "Blue", "Red"],
}
]
如果我執行:
db.fighters.find({"special_colors.2": "Red"}, {"_id": 0}).pretty()
它可以工作並返回所有文檔。
如果我執行:
db.fighters.find({"special_colors.0": "Blue"}, {"_id": 0}).pretty()
它工作並返回 first 和 second 文檔。
如果我執行:
db.fighters.find({"special_colors.1" : "Blue"}, {"_id" : 0}).pretty()
它工作並返回 第三個 文檔。
檢索所有文檔,無論 Blue 或 位於哪個位置(索引) Black,我執行以下操作並且工作正常:
db.fighters.find({"special_colors" : {$elemMatch: {$eq: "Blue"} } }, {"_id" : 0}).pretty()
情況/場景
我想申請 OR,它檢索所有文檔,其中 special_colors
有 Blue
或 Black
(無論索引的位置如何),我嘗試了許多變化,例如:
db.fighters.find({"special_colors" : {$elemMatch: {$eq: "Blue", $eq: "Black"} } }, {"_id" : 0}).pretty()
when is executed返回一些文檔,但只考慮第二部分(黑色),第一部分(藍色)被完全忽略
與
db.fighters.find({"special_colors" : {$elemMatch: {$eq: ["Blue", "Black"]} } }, {"_id" : 0, }).pretty()
when is執行返回nothing
With
db.fighters.find({"special_colors" : {$elemMatch: {$or: ["Blue", "Black"] } } }, {"_id" : 0}).pretty()
執行時出現以下錯誤消息:$or/$and/$nor entries need to be完整對象
and with:
db.fighters.find({
"special_colors": {
$or: [{$elemMatch: {$eq: "Blue"}},
{$elemMatch: {$eq: "Black"}}
]
}
}, {"_id": 0}).pretty()
執行時出現以下錯誤消息:unknown operator: $or
那麼 OR
和 AND
的正確語法是什麼?
因此:
- 檢索
special_colors
具有Blue
或Black
的所有文檔(無論索引的位置如何) - 檢索
special_colors
同時具有Blue
和Black
的所有文檔(無論位置如何索引)
special_colors
具有 Blue
或 Black
的所有文檔(無論索引的位置如何) special_colors
同時具有 Blue
和 Black
的所有文檔(無論索引的位置如何)special_colors
具有 Blue
或 Black
的所有文檔(無論索引的位置如何) special_colors
同時具有 Blue
和 Black
的所有文檔(無論索引的位置如何)參考解法
方法 1:
Retrieve all the documents where
special_colors
has eitherBlue
orBlack
(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()
$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()
Retrieve all the documents where
special_colors
has bothBlue
andBlack
(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()
$and
: (equivelant to $all
)
db.fighters.find({
$and: [
{ special_colors: "Blue" },
{ special_colors: "Black" }
]
},
{"_id" : 0}).pretty()
方法 2:
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.
And:
db.collection.find({
special_colors: {
$all: [
"Blue",
"Black"
]
}
})
Will match documents that have both colors in their array.
(by Manuel Jordan、turivishal、Tom Slabbaert)