MongoDB查询

db.users.find() select * from users

db.users.find({"age" : 27}) select * from users where age = 27

db.users.find({"username" : "joe", "age" : 27}) select * from users where "username" = "joe" and age = 27

db.users.find({}, {"username" : 1, "email" : 1}) select username, email from users

db.users.find({}, {"username" : 1, "_id" : 0}) // no case  // 即时加上了列筛选，_id也会返回；必须显式的阻止_id返回

db.users.find({"age" : {"$gte" : 18, "$lte" : 30}}) select * from users where age >=18 and age <= 30 // $lt(<) $lte(<=) $gt(>) $gte(>=)

db.users.find({"username" : {"$ne" : "joe"}}) select * from users where username <> "joe"

db.users.find({"ticket_no" : {"$in" : [725, 542, 390]}}) select * from users where ticket_no in (725, 542, 390)

db.users.find({"ticket_no" : {"$nin" : [725, 542, 390]}}) select * from users where ticket_no not in (725, 542, 390)

db.users.find({"$or" : [{"ticket_no" : 725}, {"winner" : true}]}) select * form users where ticket_no = 725 or winner = true

db.users.find({"id_num" : {"$mod" : [5, 1]}}) select * from users where (id_num mod 5) = 1

db.users.find({"$not": {"age" : 27}}) select * from users where not (age = 27)

db.users.find({"username" : {"$in" : [null], "$exists" : true}}) select * from users where username is null // 如果直接通过find({"username" : null})进行查询，那么连带"没有username"的纪录一并筛选出来

db.users.find({"name" : /joey?/i}) // 正则查询，value是符合PCRE的表达式

db.food.find({fruit : {$all : ["apple", "banana"]}}) // 对数组的查询, 字段fruit中，既包含"apple",又包含"banana"的纪录

db.food.find({"fruit.2" : "peach"}) // 对数组的查询, 字段fruit中，第3个(从0开始)元素是peach的纪录

db.food.find({"fruit" : {"$size" : 3}}) // 对数组的查询, 查询数组元素个数是3的记录，$size前面无法和其他的操作符复合使用

db.users.findOne(criteria, {"comments" : {"$slice" : 10}}) // 对数组的查询，只返回数组comments中的前十条，还可以{"$slice" : -10}， {"$slice" : [23, 10]}; 分别返回最后10条，和中间10条

db.people.find({"name.first" : "Joe", "name.last" : "Schmoe"})  // 嵌套查询

db.blog.find({"comments" : {"$elemMatch" : {"author" : "joe", "score" : {"$gte" : 5}}}}) // 嵌套查询，仅当嵌套的元素是数组时使用,

db.foo.find({"$where" : "this.x + this.y == 10"}) // 复杂的查询，$where当然是非常方便的，但效率低下。对于复杂查询，考虑的顺序应当是 正则 -> MapReduce -> $where

db.foo.find({"$where" : "function() { return this.x + this.y == 10; }"}) // $where可以支持javascript函数作为查询条件

db.foo.find().sort({"x" : 1}).limit(1).skip(10); // 返回第(10, 11]条，按"x"进行排序; 三个limit的顺序是任意的，应该尽量避免skip中使用large-number

测试数据

> 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";

    > db.user.find({y: {$regex:part, $options:"ix"}}).limit(1);
    { "_id" : ObjectId("54ffb117e9ab791b5d8f8601"), "x" : 4, "y" : [ "aaaa", "eeee" ] }

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 }

/************************************************************************************/

db.persons.insert({_id:6,books:[{type:"JS",name:"JQuery"},{type:"JS",name:"EXTJS"},{type:"DB",name:"MongoDB"}]})




db.persons.find()


db.persons.drop()


db.persons.update({"books.type":"JS"},{$set:{"books.$.author":"chubanshe"}})






var persons = [{
name:"jim",
age:25,
email:"75431457@qq.com",
c:89,m:96,e:87,
country:"USA",
books:["JS","C++","EXTJS","MONGODB"]
},
{
name:"tom",
age:25,
email:"214557457@qq.com",
c:75,m:66,e:97,
country:"USA",
books:["PHP","JAVA","EXTJS","C++"]
},
{
name:"lili",
age:26,
email:"344521457@qq.com",
c:75,m:63,e:97,
country:"USA",
books:["JS","JAVA","C#","MONGODB"]
},
{
name:"zhangsan",
age:27,
email:"2145567457@qq.com",
c:89,m:86,e:67,
country:"China",
books:["JS","JAVA","EXTJS","MONGODB"]
},
{
name:"lisi",
age:26,
email:"274521457@qq.com",
c:53,m:96,e:83,
country:"China",
books:["JS","C#","PHP","MONGODB"]
},
{
name:"wangwu",
age:27,
email:"65621457@qq.com",
c:45,m:65,e:99,
country:"China",
books:["JS","JAVA","C++","MONGODB"]
},
{
name:"zhaoliu",
age:27,
email:"214521457@qq.com",
c:99,m:96,e:97,
country:"China",
books:["JS","JAVA","EXTJS","PHP"]
},
{
name:"piaoyingjun",
age:26,
email:"piaoyingjun@uspcat.com",
c:39,m:54,e:53,
country:"Korea",
books:["JS","C#","EXTJS","MONGODB"]
},
{
name:"lizhenxian",
age:27,
email:"lizhenxian@uspcat.com",
c:35,m:56,e:47,
country:"Korea",
books:["JS","JAVA","EXTJS","MONGODB"]
},
{
name:"lixiaoli",
age:21,
email:"lixiaoli@uspcat.com",
c:36,m:86,e:32,
country:"Korea",
books:["JS","JAVA","PHP","MONGODB"]
},
{
name:"zhangsuying",
age:22,
email:"zhangsuying@uspcat.com",
c:45,m:63,e:77,
country:"Korea",
books:["JS","JAVA","C#","MONGODB"]
}]
for(var i = 0;i<persons.length;i++){
db.persons.insert(persons[i])
}
var persons = db.persons.find({name:"jim"})
while(persons.hasNext()){
obj = persons.next();
        print(obj.books.length)
} 




/*查询不含id*/
db.persons.find({},{_id:0,name:1,country:1})


/*年龄大于等于*/
db.persons.find({age:{$gt:25}},{_id:0,name:1,country:1,age:1})


db.persons.find({age:{$lte:27},age:{$gte:25}},{_id:0,name:1,country:1,age:1})


/*查询大于25小鱼27岁的*/
db.persons.find({age:{$lte:27,$gte:25}},{_id:0,name:1,country:1,age:1})


/*b不等于*/
db.persons.find({country:{$ne:"Korea"}},{_id:0,m:1,name:1,country:1})
db.persons.find({country:{$ne:"USA"}},{_id:0,m:1,name:1,country:1})
db.persons.find({},{_id:0,m:1})
/*in 用法*/
db.persons.find({country:{$in:["USA"]}},{_id:0,m:1,name:1,country:1})


/*查询英语大于90或者语文大于85的学生信息*/
db.persons.find({$or:[{c:{$gte:85}},{e:{$gte:90}}]},{_id:0,name:1,m:1,e:1,c:1})