MongoDB查詢(陣列、內嵌文件和$where)

查詢陣列

查詢陣列很容易，對於陣列，我們可以這樣理解：陣列中每一個元素都是這個鍵值對鍵的一個有效值，如下面的例子：我們要查詢出售apple的水果店:

1.  > db.fruitshop.find();  
2.  { "_id" : ObjectId("5022518d09248743250688e0"), "name" : "big fruit", "fruits" : [ "apple", "pear", "orange" ] }  
3.  { "_id" : ObjectId("502251a309248743250688e1"), "name" : "good fruit", "fruits" : [ "banana", "pear", "orange" ] }  
4.  { "_id" : ObjectId("502251c109248743250688e2"), "name" : "good fruit", "fruits" : [ "banana", "apple", "tomato" ] }  
5.  > db.fruitshop.find({"fruits":"apple"});  
6.  { "_id" : ObjectId("5022518d09248743250688e0"), "name" : "big fruit", "fruits" : [ "apple", "pear", "orange" ] }  
7.  { "_id" : ObjectId("502251c109248743250688e2"), "name" : "good fruit", "fruits" : [ "banana", "apple", "tomato" ] }  
8.  >  

我們發現只要包含蘋果的陣列都能被查詢出來。如果要通過多個元素來匹配陣列，就需要條件操作符"$all"，比如我們要查詢既賣apple又賣banana的水果店：

1.  > db.fruitshop.find();  
2.  { "_id" : ObjectId("5022518d09248743250688e0"), "name" : "big fruit", "fruits" : [ "apple", "pear", "orange" ] }  
3.  { "_id" : ObjectId("502251a309248743250688e1"), "name" : "good fruit", "fruits" : [ "banana", "pear", "orange" ] }  
4.  { "_id" : ObjectId("502251c109248743250688e2"), "name" : "good fruit", "fruits" : [ "banana", "apple", "tomato" ] }  
5.  > db.fruitshop.find({"fruits":{"$all":["apple","banana"]}});  
6.  { "_id" : ObjectId("502251c109248743250688e2"), "name" : "good fruit", "fruits" : [ "banana", "apple", "tomato" ] }  
7.  >  

我們看，使用“$all”對陣列內元素的順序沒有要求，只要全部包含的陣列都能查詢出來。陣列查詢也可以使用精確匹配的方式，即查詢條件文件中鍵值對的值也是陣列，如：

1.  { "_id" : ObjectId("5022518d09248743250688e0"), "name" : "big fruit", "fruits" : [ "apple", "pear", "orange" ] }  
2.  { "_id" : ObjectId("5022535109248743250688e4"), "name" : "fruit king", "fruits" : [ "apple", "orange", "pear" ] }  
3.  { "_id" : ObjectId("502253c109248743250688e5"), "name" : "good fruit", "fruits" : [ "apple", "orange", "pear", "banana" ] }  
4.  > db.fruitshop.find({"fruits":["apple","orange","pear"]});  
5.  { "_id" : ObjectId("5022535109248743250688e4"), "name" : "fruit king", "fruits" : [ "apple", "orange", "pear" ] }  
6.  >  

如果是精確匹配的方式，MongoDB的處理方式是完全相同的匹配，即順序與數量都要一致，上述中第一條文件和查詢條件的順序不一致，第三條文件比查詢條件文件多一個元素，都沒有被匹配成功！

對於陣列的匹配，還有一種形式是精確指定陣列中某個位置的元素匹配，我們前面提到，陣列中的索引可以作為鍵使用，如我們要匹配水果店售第二種水果是orange 的水果店：

1.  > db.fruitshop.find();  
2.  { "_id" : ObjectId("5022518d09248743250688e0"), "name" : "big fruit", "fruits" : [ "apple", "pear", "orange" ] }  
3.  { "_id" : ObjectId("5022535109248743250688e4"), "name" : "fruit king", "fruits" : [ "apple", "orange", "pear" ] }  
4.  { "_id" : ObjectId("502253c109248743250688e5"), "name" : "good fruit", "fruits" : [ "apple", "orange", "pear", "banana" ] }  
5.  > db.fruitshop.find({"fruits.1":"orange"});  
6.  { "_id" : ObjectId("5022535109248743250688e4"), "name" : "fruit king", "fruits" : [ "apple", "orange", "pear" ] }  
7.  { "_id" : ObjectId("502253c109248743250688e5"), "name" : "good fruit", "fruits" : [ "apple", "orange", "pear", "banana" ] }  
8.  >  

陣列索引從0開始，我們匹配第二種水果就用furits.1作為鍵。

"$size"條件操作符，可以用來查詢特定長度的陣列的，如我們要查詢賣3種水果的水果店：

1.  > db.fruitshop.find();  
2.  { "_id" : ObjectId("5022518d09248743250688e0"), "name" : "big fruit", "fruits" : [ "apple", "pear", "orange" ] }  
3.  { "_id" : ObjectId("5022535109248743250688e4"), "name" : "fruit king", "fruits" : [ "apple", "orange", "pear" ] }  
4.  { "_id" : ObjectId("502253c109248743250688e5"), "name" : "good fruit", "fruits" : [ "apple", "orange", "pear", "banana" ] }  
5.  > db.fruitshop.find({"fruits":{"$size":3}});  
6.  { "_id" : ObjectId("5022518d09248743250688e0"), "name" : "big fruit", "fruits" : [ "apple", "pear", "orange" ] }  
7.  { "_id" : ObjectId("5022535109248743250688e4"), "name" : "fruit king", "fruits" : [ "apple", "orange", "pear" ] }  
8.  >  

但條件操作符"$size"不能和其他操作符連用如“$gt”等，這是這個操作符的一個缺陷。使用這個操作符我們只能精確查詢某個長度的陣列。如果實際中，在查詢某個陣列時，需要按其長度範圍進行查詢，這裡推薦的做法是：在這個文件中額外增加一個“size”鍵，專門記錄其中陣列的大小，在對陣列進行"$push"操作同時，將這個“size”鍵值加1。如下所示：

1.  > db.fruitshop.find({"name":"big fruit"});  
2.  { "_id" : ObjectId("5022518d09248743250688e0"), "fruits" : [ "apple", "pear", "orange", "strawberry" ], "name" : "big fruit", "size" : 4 }  
3.  > db.fruitshop.update({"name":"big fruit"},  
4.  ... {"$push":{"fruits":"banana"}, "$inc":{"size":1}}, false, false);  
5.  > db.fruitshop.find({"name":"big fruit"});  
6.  { "_id" : ObjectId("5022518d09248743250688e0"), "fruits" : [ "apple", "pear", "orange", "strawberry", "banana" ], "name" : "big fruit", "size" : 5 }  
7.  >  

但這個方式和修改器"$addToSet"沒法配合使用，因為你無法判斷這個元素是否新增到了陣列中！

上篇提到了，find函式的第二個引數用於查詢返回哪些鍵，他還可以控制查詢返回陣列的一個子陣列，如下例：我只想查詢水果店售賣說過陣列的前兩個：

1.  > db.fruitshop.find();  
2.  { "_id" : ObjectId("5022518d09248743250688e0"), "fruits" : [ "apple", "pear", "orange", "strawberry", "banana" ], "name" : "big fruit" }  
3.  { "_id" : ObjectId("5022535109248743250688e4"), "fruits" : [ "apple", "orange", "pear" ], "name" : "fruit king" }  
4.  { "_id" : ObjectId("502253c109248743250688e5"), "fruits" : [ "apple", "orange", "pear", "banana" ], "name" : "good fruit" }  
5.  > db.fruitshop.find({}, {"fruits":{"$slice":2}});  
6.  { "_id" : ObjectId("5022518d09248743250688e0"), "fruits" : [ "apple", "pear" ], "name" : "big fruit" }  
7.  { "_id" : ObjectId("5022535109248743250688e4"), "fruits" : [ "apple", "orange" ], "name" : "fruit king" }  
8.  { "_id" : ObjectId("502253c109248743250688e5"), "fruits" : [ "apple", "orange" ], "name" : "good fruit" }  
9.  >  

“$slice”也可以從後面擷取，用複數即可，如-1表明擷取最後一個；還可以擷取中間部分，如[2,3]，即跳過前兩個，擷取3個，如果剩餘不足3個，就全部返回！

1.  > db.fruitshop.find();  
2.  { "_id" : ObjectId("5022518d09248743250688e0"), "fruits" : [ "apple", "pear", "orange", "strawberry", "banana" ], "name" : "big fruit" }  
3.  { "_id" : ObjectId("5022535109248743250688e4"), "fruits" : [ "apple", "orange", "pear" ], "name" : "fruit king" }  
4.  { "_id" : ObjectId("502253c109248743250688e5"), "fruits" : [ "apple", "orange", "pear", "banana" ], "name" : "good fruit" }  
5.  > db.fruitshop.find({}, {"fruits":{"$slice":-1}});  
6.  { "_id" : ObjectId("5022518d09248743250688e0"), "fruits" : [ "banana" ], "name" : "big fruit" }  
7.  { "_id" : ObjectId("5022535109248743250688e4"), "fruits" : [ "pear" ], "name" : "fruit king" }  
8.  { "_id" : ObjectId("502253c109248743250688e5"), "fruits" : [ "banana" ], "name" : "good fruit" }  
9.  > db.fruitshop.find({}, {"fruits":{"$slice":[3,6]}});  
10.  { "_id" : ObjectId("5022518d09248743250688e0"), "fruits" : [ "strawberry", "banana" ], "name" : "big fruit" }  
11.  { "_id" : ObjectId("5022535109248743250688e4"), "fruits" : [ ], "name" : "fruit king" }  
12.  { "_id" : ObjectId("502253c109248743250688e5"), "fruits" : [ "banana" ], "name" : "good fruit" }  
13.  >  

如果第二個引數中有個鍵使用了條件操作符"$slice"，則預設查詢會返回所有的鍵，如果此時你要忽略哪些鍵，可以手動指明！如：

1.  > db.fruitshop.find({}, {"fruits":{"$slice":[3,6]}, "name":0, "_id":0});  
2.  { "fruits" : [ "strawberry", "banana" ] }  
3.  { "fruits" : [ ] }  
4.  { "fruits" : [ "banana" ] }  
5.  >  

查詢內嵌文件

查詢文件有兩種方式，一種是完全匹查詢，另一種是針對鍵值對查詢！內嵌文件的完全匹配查詢和陣列的完全匹配查詢一樣，內嵌文件內鍵值對的數量，順序都必須一致才會匹配，如下例：

1.  > db.staff.find();  
2.  { "_id" : ObjectId("50225fc909248743250688e6"), "name" : { "first" : "joe", "middle" : "bush", "last" : "Schmoe" }, "age" : 45 }  
3.  { "_id" : ObjectId("50225fe209248743250688e7"), "name" : { "first" : "joe", "middle" : "bush" }, "age" : 35 }  
4.  { "_id" : ObjectId("50225fff09248743250688e8"), "name" : { "middle" : "bush", "first" : "joe" }, "age" : 25 }  
5.  > db.staff.find({"name":{"first":"joe","middle":"bush"}});  
6.  { "_id" : ObjectId("50225fe209248743250688e7"), "name" : { "first" : "joe", "middle" : "bush" }, "age" : 35 }  
7.  >  

針對內嵌文件特定鍵值對的查詢是最常用的！通過點表示法來精確表示內嵌文件的鍵：

1.  > db.staff.find();  
2.  { "_id" : ObjectId("50225fc909248743250688e6"), "name" : { "first" : "joe", "middle" : "bush", "last" : "Schmoe" }, "age" : 45 }  
3.  { "_id" : ObjectId("50225fe209248743250688e7"), "name" : { "first" : "joe", "middle" : "bush" }, "age" : 35 }  
4.  { "_id" : ObjectId("50225fff09248743250688e8"), "name" : { "middle" : "bush", "first" : "joe" }, "age" : 25 }  
5.  > db.staff.find({"name.first":"joe", "name.middle":"bush"});  
6.  { "_id" : ObjectId("50225fc909248743250688e6"), "name" : { "first" : "joe", "middle" : "bush", "last" : "Schmoe" }, "age" : 45 }  
7.  { "_id" : ObjectId("50225fe209248743250688e7"), "name" : { "first" : "joe", "middle" : "bush" }, "age" : 35 }  
8.  { "_id" : ObjectId("50225fff09248743250688e8"), "name" : { "middle" : "bush", "first" : "joe" }, "age" : 25 }  
9.  >  

我們看，這樣查詢，所有有效文件均被查詢到了！通過點表示法，可以表示深入到內嵌文件內部的鍵！利用“點表示法”來查詢內嵌文件，這也約束了在插入文件時，任何鍵都不能包含“.” !!

當內嵌文件變得複雜後，如鍵的值為內嵌文件的陣列，這種內嵌文件的匹配需要一些技巧，如下例：

1.  > db.blogs.findOne();  
2.  {  
3.  "_id" : ObjectId("502262ab09248743250688ea"),  
4.  "content" : ".....",  
5.  "comment" : [  
6.  {  
7.  "author" : "joe",  
8.  "score" : 3,  
9.  "comment" : "just so so!"  
10.  },  
11.  {  
12.  "author" : "jimmy",  
13.  "score" : 5,  
14.  "comment" : "cool! good!"  
15.  }  
16.  ]  
17.  }  
18.  > db.blogs.find({"comment.author":"joe", "comment.score":{"$gte":5}});  
19.  { "_id" : ObjectId("502262ab09248743250688ea"), "content" : ".....", "comment" : [      {       "author" : "joe",       "score" : 3,    "comment" : "j  
20.  ust so so!" },  {       "author" : "jimmy",     "score" : 5,    "comment" : "cool! good!" } ] }  
21.  >  

我們想要查詢評論中有叫“joe”並且其給出的分數超過5分的blog文件，但我們利用“點表示法”直接寫是有問題的，因為這條文件有兩條評論，一條的作者名字叫“joe”但分數只有3，一條作者名字叫“jimmy”，分數卻給了5！也就是這條查詢條件和陣列中不同的文件進行了匹配！這不是我們想要的，我們這裡是要使用一組條件而不是單個指明每個鍵，使用條件操作符“$elemMatch”即可！他能將一組條件限定到陣列中單條文件的匹配上：

1.  > db.blogs.findOne();  
2.  {  
3.  "_id" : ObjectId("502262ab09248743250688ea"),  
4.  "content" : ".....",  
5.  "comment" : [  
6.  {  
7.  "author" : "joe",  
8.  "score" : 3,  
9.  "comment" : "just so so!"  
10.  },  
11.  {  
12.  "author" : "jimmy",  
13.  "score" : 5,  
14.  "comment" : "cool! good!"  
15.  }  
16.  ]  
17.  }  
18.  > db.blogs.find({"comment":{"$elemMatch":{"author":"joe", "score":{"$gte":5}}}});  
19.  > db.blogs.find({"comment":{"$elemMatch":{"author":"joe", "score":{"$gte":3}}}});  
20.  { "_id" : ObjectId("502262ab09248743250688ea"), "content" : ".....", "comment" : [      {       "author" : "joe",       "score" : 3,    "comment" : "j  
21.  ust so so!" },  {       "author" : "jimmy",     "score" : 5,    "comment" : "cool! good!" } ] }  
22.  >  

這樣做，結果是正確的！利用條件操作符“$elemMatch”可以組合一組條件，並且還能達到的“點表示法”的模糊查詢的效果！

$where

上面提到的所有的鍵值對的查詢方式，我們也可以看出，已經很強大了！但如果實際中真的遇到一種情況無法用上述方式實現時，不用慌，MongoDB為我們提供了終極武器："$where"，用他可以執行任意JavaScript作為查詢的一部分！最典型的應用：一個文件，如果有兩個鍵的值相等，就選出來，否則不選：

1.  > db.fruitprice.find();  
2.  { "_id" : ObjectId("50226b4c3becfacce6a22a5b"), "apple" : 10, "banana" : 6, "pear" : 3 }  
3.  { "_id" : ObjectId("50226ba63becfacce6a22a5c"), "apple" : 10, "watermelon" : 3, "pear" : 3 }  
4.  > db.fruitprice.find({"$where":function () {  
5.  ... for(var current in this){  
6.  ...     for(var other in this){  
7.  ...         if(current != other && this[current] == this[other]){  
8.  ...             return true;  
9.  ...         }  
10.  ...     }  
11.  ... }  
12.  ... return false;  
13.  ... }});  
14.  { "_id" : ObjectId("50226ba63becfacce6a22a5c"), "apple" : 10, "watermelon" : 3, "pear" : 3 }  
15.  >  

我們可以看出，使用"$where"其實就是寫了一個javascript函式，MongoDB在查詢時，會將每個文件轉換成一個javascript物件，然後扔到這個函式中去執行，通過返回結果來判斷其是否匹配！在實際使用中，儘量避免使用”$where" 條件操作符，因為其效能很差！在執行過程中，需要把每個檔案轉化為javascript物件！如果不可避免，則儘量這樣寫：find({”other“：”......“，......，“$where”:""})，即將"$where"放最後，作為結果調優，讓常規查詢作為前置過濾條件！這樣能減少一些效能損失！

我們這裡還可以發現，“$where”條件操作符也是作為外層文件的鍵使用，昨天說“$or”條件操作符是被作為外層文件的鍵使用。其餘目前遇到的條件操作符都是被作為內層文件的鍵使用！