[Database]MongoDB(2)高级查询条件操作符

MongoDB支持多种复杂的查询方式，能实现大多数T-SQL功能，远不是Key-Value之类的NoSQLDB所能比拟的。ConditionalOperators:$slice切片

MongoDB 支持多种复杂的查询方式，能实现大多数 T-SQL 功能，远不是 Key-Value 之类的 NoSQL DB 所能比拟的。Conditional Operators : $slice //切片Conditional Operators : $lt <, $lte , $gte >=Conditional Operator : $ne //不等于Conditional Operator : $in //属于Conditional Operator : $nin //不属于Conditional Operator : $mod //取模运算Conditional Operator:   $all  //全部属于Conditional Operator : $size //数量Conditional Operator: $exists //字段存在Conditional Operator: $type //字段类型Conditional Operator: $or // 或Regular Expressions //正则表达式Value in an Array // 数组中的值Conditional Operator: $elemMatch //要素符合Value in an Embedded Object //内嵌对象中的值Meta operator: $not //不是Javascript Expressions and $where //sort() //排序limit() //限制取数据条数skip() //跳过一定数值开始取snapshot() //count() // 数量group() //分组

准备数据

In [9]: dbOut[9]: Database(Connection('localhost', 27017), u'test')In [10]: table = db.table_abeenIn [11]: tableOut[11]: Collection(Database(Connection('localhost', 27017), u'test'), u'table_abeen')In [12]: table.insert({"name":"abeen", "age":27})Sun Aug 8 23:14:20 connection accepted from 127.0.0.1:46143 #27Out[12]: ObjectId('4c5f9cbc421aa90fb9000000')In [14]: table.insert({"name":"shanshan", "age":22})Out[14]: ObjectId('4c5f9ccb421aa90fb9000001')

Conditional Operator: $ne (not equal)

//查找name不等于abeen的信息In [24]: list(table.find({"name":{"$ne":"abeen"}}))Out[24]:[{u'_id': ObjectId('4c5f9ccb421aa90fb9000001'),u'age': 22,u'name': u'shanshan'},{u'_id': ObjectId('4c5f9d2d421aa90fb9000002'),u'age': 22,u'name': u'shanshan2'},{u'_id': ObjectId('4c5f9d34421aa90fb9000003'),u'age': 23,u'name': u'shanshan3'}]

Conditional Operator: $gt $lt(gt= greater than, lt=less than)

//查找name不等于abeen,并且age大于22的In [29]: list(table.find({"name": {"$ne": "abeen"}, "age":{"$gt": 22}}))Out[29]:[{u'_id': ObjectId('4c5f9d34421aa90fb9000003'), u'age': 23, u'name': u'shanshan3'}]

获取子集 $ne  $slice 

//select "age" from table where name = "abeen"In [42]: list(table.find({"name": "abeen"}, {"age" : 1}))Out[42]: [{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'age': 27}]//get all posts about mongodb without "age"In [43]: list(table.find({"name": "abeen"}, {"age" : 0}))Out[43]: [{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'name': u'abeen'}]//name不等于abeen的"age"信息,取前5条In [48]: list(table.find({"name": {"$ne":"abeen"}}, {"age":{"$slice":5}}))//取name信息,从第10条开始取20条In [54]: list(table.find({}, {"name": {"$slice": [10,20]}}))//取name信息,从后20条开始取10条In [55]: list(table.find({}, {"name": {"$slice": [-20,10]}}))

取数值范围

//age大于23的In [56]: list(table.find({"age":{"$gt":23}}))Out[56]: [{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'age': 27, u'name': u'abeen'}]//age小于23的In [57]: list(table.find({"age":{"$lt":23}}))Out[57]:[{u'_id': ObjectId('4c5f9ccb421aa90fb9000001'), u'age': 22, u'name': u'shanshan'}, {u'_id': ObjectId('4c5f9d2d421aa90fb9000002'), u'age': 22, u'name': u'shanshan2'}]//age大于等于23的In [58]: list(table.find({"age":{"$gte":23}}))Out[58]:[{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'age': 27, u'name': u'abeen'}, {u'_id': ObjectId('4c5f9d34421aa90fb9000003'), u'age': 23, u'name': u'shanshan3'}, {u'_id': ObjectId('4c5fa2ab421aa90fb9000004'), u'address': u'da zhong si', u'age': 23, u'name': u'shanshan3'}]//age小于等于23的In [59]: list(table.find({"age":{"$lte":23}}))Out[59]:[{u'_id': ObjectId('4c5f9ccb421aa90fb9000001'), u'age': 22, u'name': u'shanshan'}, {u'_id': ObjectId('4c5f9d2d421aa90fb9000002'), u'age': 22, u'name': u'shanshan2'}, {u'_id': ObjectId('4c5f9d34421aa90fb9000003'), u'age': 23, u'name': u'shanshan3'}, {u'_id': ObjectId('4c5fa2ab421aa90fb9000004'), u'address': u'da zhong si', u'age': 23, u'name': u'shanshan3'}]

Conditional Operator: $gt

//22 < age < 25的In [63]: list(table.find({"age": {"$gt":22, "$lt":25}}))Out[63]:[{u'_id': ObjectId('4c5f9d34421aa90fb9000003'), u'age': 23, u'name': u'shanshan3'}, {u'_id': ObjectId('4c5fa2ab421aa90fb9000004'), u'address': u'da zhong si', u'age': 23, u'name': u'shanshan3'}]

Conditional Operator : $in

//name在列表["abeen","ab","b"]里面的In [67]: list(table.find({"name":{"$in":["abeen","ab","b"]}}))Out[67]: [{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'age': 27, u'name': u'abeen'}]//name在列表["abeen","ab","b"]里面的,限制取1条数据In [69]: list(table.find({"name":{"$in":["abeen","ab","b","shanshan"]}}).limit(1))Out[69]: [{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'age': 27, u'name': u'abeen'}]

Conditional Operator : $nin (not in)

//name不在列表["abeen","ab","b"]里面的In [70]: list(table.find({"name":{"$nin":["abeen","ab","b"]}}))Out[70]:[{u'_id': ObjectId('4c5f9ccb421aa90fb9000001'), u'age': 22, u'name': u'shanshan'}, {u'_id': ObjectId('4c5f9d2d421aa90fb9000002'), u'age': 22, u'name': u'shanshan2'}, {u'_id': ObjectId('4c5f9d34421aa90fb9000003'), u'age': 23, u'name': u'shanshan3'}, {u'_id': ObjectId('4c5fa2ab421aa90fb9000004'), u'address': u'da zhong si', u'age': 23, u'name': u'shanshan3'}]

Conditional Operator: $mod

// 查找age除10模等于1的In [71]: list(table.find({"age":{"$mod":[10,1]}}))

Conditional Operator: $all

//取name包含所有["abeen","a","b"]的信息In [77]: list(table.find({"name":{"$all":["abeen","a","b"]}}))Out[77]:[{u'_id': ObjectId('4c5facc6421aa90fb9000005'), u'name': [u'abeen', u'a', u'b', u'e', u'e', u'n']}]

Conditional Operator: $size

//取name元素数和$size数相同的信息In [81]: list(table.find({"name":{"$size": 6}}))Out[81]:[{u'_id': ObjectId('4c5facc6421aa90fb9000005'), u'name': [u'abeen', u'a', u'b', u'e', u'e', u'n']}]

Conditional Operator: $exists

//取name存在的信息In [83]: list(table.find({"name":{"$exists": True}}))Out[83]:[{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'age': 27, u'name': u'abeen'}, {u'_id': ObjectId('4c5f9ccb421aa90fb9000001'), u'age': 22, u'name': u'shanshan'}, {u'_id': ObjectId('4c5f9d2d421aa90fb9000002'), u'age': 22, u'name': u'shanshan2'}, {u'_id': ObjectId('4c5f9d34421aa90fb9000003'), u'age': 23, u'name': u'shanshan3'}, {u'_id': ObjectId('4c5fa2ab421aa90fb9000004'), u'address': u'da zhong si', u'age': 23, u'name': u'shanshan3'}, {u'_id': ObjectId('4c5facc6421aa90fb9000005'), u'name': [u'abeen', u'a', u'b', u'e', u'e', u'n']}]//取name不存在信息In [84]: list(table.find({"name":{"$exists": False}}))Out[84]: []

Conditional Operator: $type

//name类型为字符串的In [88]: list(table.find({"name":{"$type": 2}}))

type对应该类型表如下:

Conditional Operator: $or

//查找name等于abeen或等于shanshan的信息In [95]: list(table.find({"$or" :[{"name": "abeen"}, {"name":"shanshan"}]}))Out[95]: []//查找age等于22,或name等于abeen或等于shanshan的信息In [96]: list(table.find({"age":22, "$or" :[{"name": "abeen"}, {"name":"shanshan"}]}))Out[96]: []

Regular Expressions

//利用正则查询In [114]: list(table.find({"name": {"$regex": r".*ee.*"}}))Out[114]:[{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'age': 27, u'name': u'abeen'}, {u'_id': ObjectId('4c5facc6421aa90fb9000005'), u'name': [u'abeen', u'a', u'b', u'e', u'e', u'n']}]正则表达式标记:i: 忽略大小写。m: 默认为单行处理，此标记表示多行。x: 扩展。

 Conditional Operator: $elemMatch

In [135]: list(table.find( { "age" : {"$elemMatch": {"name": {"$regex": r".*ee.*"}, 　　　　　　"age":{"$gt":22}}}}))

Value in an Embedded Object

//查找内部对象信息,//查找内部对象info的name等于abeen的信息In [217]: list(table.find({"info.name": "abeen"}))Out[217]:[{u'_id': ObjectId('4c5fcd7e421aa90fb9000007'), u'info': {u'address': u'beijing', u'age': 28, u'name': u'abeen'}, u'name': u'abeen_object'}]

Meta operator: $not

//查询age不在大于23的范围内的信息In [160]: list(table.find({"age": {"$not":{"$gt": 23}}}))Out[160]:[{u'_id': ObjectId('4c5f9ccb421aa90fb9000001'), u'age': 22, u'name': u'shanshan'}, {u'_id': ObjectId('4c5f9d2d421aa90fb9000002'), u'age': 22, u'name': u'shanshan2'}, {u'_id': ObjectId('4c5f9d34421aa90fb9000003'), u'age': 23, u'name': u'shanshan3'}, {u'_id': ObjectId('4c5fa2ab421aa90fb9000004'), u'address': u'da zhong si', u'age': 23, u'name': u'shanshan3'}, {u'_id': ObjectId('4c5facc6421aa90fb9000005'), u'name': [u'abeen', u'a', u'b', u'e', u'e', u'n']}]

Javascript Expressions and $where

//age大于23的In [164]: list(table.find({"age": {"$gt":23}}))Out[164]:[{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'age': 27, u'name': u'abeen'}, {u'_id': ObjectId('4c5fae95421aa90fb9000006'), u'age': 25, u'name': u''}]//age大于23的In [165]: list(table.find({"$where": "this.age > 23"}))Out[165]:[{u'_id': ObjectId('4c5f9cbc421aa90fb9000000'), u'age': 27, u'name': u'abeen'}, {u'_id': ObjectId('4c5fae95421aa90fb9000006'), u'age': 25, u'name': u''}]

//skip()  limit()

In [204]: result = table.find().skip(2).limit(3)In [205]: for r in result : print r{u'age': 22, u'_id': ObjectId('4c5f9d2d421aa90fb9000002'), u'name': u'shanshan2'}{u'age': 23, u'_id': ObjectId('4c5f9d34421aa90fb9000003'), u'name': u'shanshan3'}{u'age': 23, u'_id': ObjectId('4c5fa2ab421aa90fb9000004'), u'name': u'shanshan3', 　　　　u'address': u'da zhong si'}

 

更多信息

http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-Introduction