mongodb查詢

測試資料

> var arr1 = ["aaaa", "bbbb", "cccc", "dddd"];
> var arr2 = ["eeee", "ffff", "gggg", "hhhh"];
> for (var i = 1; i <= 200; i++) {
...    db.user.insert( { x : i, y : [arr1[i%4], arr2[i%4]]} )
... }
WriteResult({ "nInserted" : 1 })

> db.user.findOne({x: 1})
{
        "_id" : ObjectId("54ffb117e9ab791b5d8f85fe"),
        "x" : 1,
        "y" : [
                "bbbb",
                "ffff"
        ]
}

db.user.insert({x: 999, y:["a","b","c","d","e","f"], z:"zzzzzzzzzz"});
db.user.insert({x: 9999, y:[{a: 1}, {b: 2}], z:"zzzzzzzzzz"});
db.user.insert({x: 9999, y:[{a: 10}, {b: 20}], z:"zzzzzzzzzz"});
db.user.insert({x: 9999, y:[{a: 100}, {b: 200}], z:"zzzzzzzzzz"});

查詢函式 db.集合名.find()

> db.user.find
function ( query , fields , limit , skip, batchSize, options ){
    var cursor = new DBQuery( this._mongo , this._db , this ,
                        this._fullName , this._massageObject( query ) , fields , limit , skip , batchSize , options || this.getQueryOptions() );

    var connObj = this.getMongo();
    var readPrefMode = connObj.getReadPrefMode();
    if (readPrefMode != null) {
        cursor.readPref(readPrefMode, connObj.getReadPrefTagSet());
    }

    return cursor;
}

查詢條件

支援的條件

$eq   =
$gt   >
$gte  >=
$lt   <
$lte  <=
$ne   <>
$in   in
$nin  not in

x大於188

y包含"gggg"

> db.user.find({x:{$gt: 188}, y:"gggg"})
{ "_id" : ObjectId("54ffb16de9ab791b5d8f86bb"), "x" : 190, "y" : [ "cccc", "gggg" ] }
{ "_id" : ObjectId("54ffb16de9ab791b5d8f86bf"), "x" : 194, "y" : [ "cccc", "gggg" ] }
{ "_id" : ObjectId("54ffb16de9ab791b5d8f86c3"), "x" : 198, "y" : [ "cccc", "gggg" ] }

x大於10，小於22

整個y陣列是["bbbb", "ffff"]

同時按x倒序排序

輸出前2行

> db.user.find({x:{$lt: 22, $lt: 10}, y:{$all: ["bbbb", "ffff"]}}).sort({x: -1}).limit(2)
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8606"), "x" : 9, "y" : [ "bbbb", "ffff" ] }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8602"), "x" : 5, "y" : [ "bbbb", "ffff" ] }

$all也可以用下面的形式代替

> db.user.findOne({y:["aaaa","eeee"]});
{
        "_id" : ObjectId("54ffb117e9ab791b5d8f8601"),
        "x" : 4,
        "y" : [
                "aaaa",
                "eeee"
        ]
}

"in"條件

> db.user.find({x:{$lt: 10}, y:{$in: ["aaaa", "ffff"]}});
{ "_id" : ObjectId("54ffb117e9ab791b5d8f85fe"), "x" : 1, "y" : [ "bbbb", "ffff" ] }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8601"), "x" : 4, "y" : [ "aaaa", "eeee" ] }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8602"), "x" : 5, "y" : [ "bbbb", "ffff" ] }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8605"), "x" : 8, "y" : [ "aaaa", "eeee" ] }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8606"), "x" : 9, "y" : [ "bbbb", "ffff" ] }

“或”條件

找出x<5或者x>195的

> db.user.find({$or: [{x:{$lt:5}}, {x:{$gt:195}}]})
{ "_id" : ObjectId("54ffa67be9ab791b5d8f85fd"), "x" : 3 }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f85fe"), "x" : 1, "y" : [ "bbbb", "ffff" ] }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f85ff"), "x" : 2, "y" : [ "cccc", "gggg" ] }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8600"), "x" : 3, "y" : [ "dddd", "hhhh" ] }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8601"), "x" : 4, "y" : [ "aaaa", "eeee" ] }
{ "_id" : ObjectId("54ffb16de9ab791b5d8f86c1"), "x" : 196, "y" : [ "aaaa", "eeee" ] }
{ "_id" : ObjectId("54ffb16de9ab791b5d8f86c2"), "x" : 197, "y" : [ "bbbb", "ffff" ] }
{ "_id" : ObjectId("54ffb16de9ab791b5d8f86c3"), "x" : 198, "y" : [ "cccc", "gggg" ] }
{ "_id" : ObjectId("54ffb16de9ab791b5d8f86c4"), "x" : 199, "y" : [ "dddd", "hhhh" ] }
{ "_id" : ObjectId("54ffb16de9ab791b5d8f86c5"), "x" : 200, "y" : [ "aaaa", "eeee" ] }
{ "_id" : ObjectId("550154f11b048c7db6481552"), "x" : 999, "y" : [ "a", "b", "c", "d", "e", "f" ], "z" : "zzzzzzzzzz" }
{ "_id" : ObjectId("550158de3d7e7d39fc067da0"), "x" : 9999, "y" : [ { "a" : 10 }, { "b" : 20 } ], "z" : "zzzzzzzzzz" }
{ "_id" : ObjectId("550158f63d7e7d39fc067da1"), "x" : 9999, "y" : [ { "a" : 100 }, { "b" : 200 } ], "z" : "zzzzzzzzzz" }
{ "_id" : ObjectId("550158fc3d7e7d39fc067da2"), "x" : 9999, "y" : [ { "a" : 1 }, { "b" : 2 } ], "z" : "zzzzzzzzzz" }

查詢巢狀文件

完整的

> db.obj.findOne()
{
        "_id" : ObjectId("5502a2ef773fc1f962baf14b"),
        "name" : "obj1",
        "arr" : [
                {
                        "elemName" : "elem1",
                        "elemValue" : 1
                },
                {
                        "elemName" : "elem2",
                        "elemValue" : 2
                }
        ],
        "subObj" : {
                "subName" : "subName1",
                "subArr" : [
                        "subElem1",
                        "subElem2",
                        "subElem3"
                ]
        }
}

按subObj.subName查詢。雙引號不能省略。

> db.obj.find({"subObj.subName":"subName1"})
{ "_id" : ObjectId("5502a2ef773fc1f962baf14b"), "name" : "obj1", "arr" : [ { "elemName" : "elem1", "elemValue" : 1 }, { "elemName" : "elem2", "elemValue" : 2 } ], "subObj" : { "subName" : "subName1", "subArr" : [ "subElem1", "subElem2", "subElem3" ] } }

按subArr查詢

> db.obj.find({"subObj.subArr":"subElem3"})
{ "_id" : ObjectId("5502a2ef773fc1f962baf14b"), "name" : "obj1", "arr" : [ { "elemName" : "elem1", "elemValue" : 1 }, { "elemName" : "elem2", "elemValue" : 2 } ], "subObj" : { "subName" : "subName1", "subArr" : [ "subElem1", "subElem2", "subElem3" ] } }

用正規表示式做條件

找出y包含以多個A開頭的元素的記錄。正規表示式後面的i意思是不分大小寫。

> db.user.find({y: {$regex:/^A+/, $options:"i"}}).limit(1)
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8601"), "x" : 4, "y" : [ "aaaa", "eeee" ] }

也可以簡寫成：

> db.user.find({y: /^A+/i}).limit(1)
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8601"), "x" : 4, "y" : [ "aaaa", "eeee" ] }

元素以a或A開頭，同時不等於aaaa

> db.user.find({y: {$regex:/^A+ #A and a/, $options:"ix", $ne:"aaaa"}}).limit(1)
{ "_id" : ObjectId("550154f11b048c7db6481552"), "x" : 999, "y" : [ "a", "b", "c", "d", "e", "f" ], "z" : "zzzzzzzzzz" }

options包括：

i：不區分大小寫

m：如果表示式有^xxx，或者xxx$，如果加上m，則/^x/和/x$/就能匹配aaa\nx和x\naaa這種帶換行的情況。

x：讓正規表示式可以註釋。要用這個選項，必須在find裡用$options:x的形式。

    例如

    > var part = "^A #a and A";

s：讓符號“.”能也能匹配新一行“\n”。必須在find裡用$options:s的形式。

用程式碼方式自定義判斷條件

> db.user.find({$where: "this.x >= 100 && this.y[0] == 'aaaa'"});
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8661"), "x" : 100, "y" : [ "aaaa", "eeee" ] }
{ "_id" : ObjectId("54ffb16de9ab791b5d8f8665"), "x" : 104, "y" : [ "aaaa", "eeee" ] }

最無敵的方式是自己定義函式

> db.user.find({$where:
...   function()
...   {
...     if(this.y == null || this.y == undefined || this.y.length == 0) return false;
...     else if(this.x <= 10 && this.y[1] == 'eeee') return true;
...     else return false;
...   }
... });
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8601"), "x" : 4, "y" : [ "aaaa", "eeee" ] }
{ "_id" : ObjectId("54ffb117e9ab791b5d8f8605"), "x" : 8, "y" : [ "aaaa", "eeee" ] }

欄位投影

不包含_id

> db.user.find({x:{$lt: 3}}, { _id: 0})
{ "x" : 1, "y" : [ "bbbb", "ffff" ] }
{ "x" : 2, "y" : [ "cccc", "gggg" ] }

只顯示x

> db.user.find({x:{$lt: 3}}, { x:1, _id:0})
{ "x" : 1 }
{ "x" : 2 }

除了x，其它的都顯示

> db.user.find({x:{$lt: 3}}, {x:0, _id: 0})
{ "y" : [ "bbbb", "ffff" ] }
{ "y" : [ "cccc", "gggg" ] }

投影裡除了_id以外，要麼全是1，要麼全是0，否則就報錯

> db.user.find({x:{$lt: 3}}, {x:0, y:1})
Error: error: {
        "$err" : "Can't canonicalize query: BadValue Projection cannot have a mix of inclusion and exclusion.",
        "code" : 17287
}

對於陣列，用$slice控制只返回前幾個元素還是後幾個元素

返回前面數第一個元素

> db.user.find({x:{$lt: 3}}, {y: {$slice: 1}, x:0, _id: 0})
{ "y" : [ "bbbb" ] }
{ "y" : [ "cccc" ] }
返回後面數第一個元素 
> db.user.find({x:{$lt: 3}}, {y: {$slice: -1}, x:0, _id: 0})
{ "y" : [ "ffff" ] }
{ "y" : [ "gggg" ] }

$slice後面跟陣列，可以靈活控制返回哪些資料

先看完整陣列

> db.user.find( {x: 999}, {y:1, _id:0})
{ "y" : [ "a", "b", "c", "d", "e", "f" ] }

如果從前面數，就是從0開始，[2,2]的意思是從第3個元素開始，返回2個元素

> db.user.find( {x: 999}, { y: { $slice: [2,2]}, _id:0, x:0, z:0} )
{ "y" : [ "c", "d" ] }

如果是從後面數，就是從-1開始，[-3,2]就是從倒數第3個開始，返回2個元素。
> db.user.find( {x: 999}, { y: { $slice: [-3,2]}, _id:0, x:0, z:0} )
{ "y" : [ "d", "e" ] }

過濾陣列裡的文件元素

先看完整的

> db.user.find({x:9999}, {z:0, _id:0}).sort({y: 1})
{ "x" : 9999, "y" : [ { "a" : 1 }, { "b" : 2 } ] }
{ "x" : 9999, "y" : [ { "a" : 10 }, { "b" : 20 } ] }
{ "x" : 9999, "y" : [ { "a" : 100 }, { "b" : 200 } ] }

只顯示包含{a:10}的y

> db.user.find({x:9999}, {y:{$elemMatch:{a:10}}, z:0, _id:0})
{ "x" : 9999, "y" : [ { "a" : 10 } ] }
{ "x" : 9999 }
{ "x" : 9999 }