mongodb 查询条件

转自：http://www.ttlsa.com/mongodb/mongodb-conditional-operators/

条件操作符，"$lt", "$lte", "$gt", "$gte", "$ne"就是全部的比较操作符，对应于"<", "<=", ">", ">=","!="。

原子操作符："$and“, "$or“, "$nor“。

or查询有两种方式：一种是用$in来查询一个键的多个值，另一种是用$or来完成多个键值的任意给定值。$in相当于SQL语句的in操作。

$nin不属于。

$not与正则表达式联合使用时候极其有用，用来查询哪些与特定模式不匹配的文档。

$slice相当于数组函数的切片，检索一个数组文档并获取数组的一部分。限制集合中大量元素节省带宽。理论上可以通过 limit() 和 skip() 函数来实现，但是，对于数组就无能为力了。 $slice可以指定两个参数。第一个参数表示要返回的元素总数。第二个参数是可选的。如果使用的话，第一个参数定义的是偏移量，而第二个参数是限定的个数。第二个参数还可以指定一个负数。

$mod取摸操作。

$size操作符允许对结果进行筛选，匹配指定的元素数的数组。

$exists操作符允许返回一个特定的对象。注意：当前版本$exists是无法使用索引的，因此，使用它需要全表扫描。

$type操作符允许基于BSON类型来匹配结果。

1. 插入一些数据

1 2 3 4 5 > use ttlsa_com switched to db ttlsa_com > db . mediaCollection . insert ( { "Type" : "DVD" , "Title" : "Matrix, The" , "Released" : 1999 , "Cast" : [ "Keanu Reeves" , "Carry-Anne Moss" , "Laurence Fishburne" , "Hugo Weaving" , "Gloria Foster" , "Joe Pantoliano" ] } ) > db . mediaCollection . insert ( { "Type" : "DVD" , Title : "Blade Runner" , Released : 1982 } ) > db . mediaCollection . insert ( { "Type" : "DVD" , Title : "Toy Story 3" , Released : 2010 } )

2. $gt (greater than)

1 2 3 4 5 6 7 8 9 > db . mediaCollection . find ( { Released : { $ gt : 2000 } } , { "Cast" : 0 } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ]

3. $gte(greater than or equal to)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 > db . mediaCollection . find ( { Released : { $ gte : 1999 } } , { "Cast" : 0 } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999          } ,          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ]

4. $lt (less than)

1 2 3 4 5 6 7 8 9 > db . mediaCollection . find ( { Released : { $ lt : 1999 } } , { "Cast" : 0 } ) . toArray ( ) [          {                  "_id" : ObjectId ( "5354823fd85b463e729a2e58" ) ,                  "Type" : "DVD" ,                  "Title" : "Blade Runner" ,                  "Released" : 1982          } ]

5. $lte (less than or equal to)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 > db . mediaCollection . find ( { Released : { $ lte : 1999 } } , { "Cast" : 0 } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999          } ,          {                  "_id" : ObjectId ( "5354823fd85b463e729a2e58" ) ,                  "Type" : "DVD" ,                  "Title" : "Blade Runner" ,                  "Released" : 1982          } ]

6. 组合使用

1 2 3 4 5 6 7 8 9 > db . mediaCollection . find ( { Released : { $ gte : 1990 , $ lt : 2010 } } , { "Cast" : 0 } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999          } ]

7. $ne (not equals)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 > db . mediaCollection . find ( { Type : "DVD" } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Keanu Reeves" ,                          "Carry-Anne Moss" ,                          "Laurence Fishburne" ,                          "Hugo Weaving" ,                          "Gloria Foster" ,                          "Joe Pantoliano"                  ]          } ,          {                  "_id" : ObjectId ( "5354823fd85b463e729a2e58" ) ,                  "Type" : "DVD" ,                  "Title" : "Blade Runner" ,                  "Released" : 1982          } ,          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ] > db . mediaCollection . find ( { Type : "DVD" , Released : { $ ne : 1999 } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "5354823fd85b463e729a2e58" ) ,                  "Type" : "DVD" ,                  "Title" : "Blade Runner" ,                  "Released" : 1982          } ,          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ]

8. $in/$or

1 2 3 4 5 6 7 8 9 > db . mediaCollection . find ( { Released : { $ in : [ 1999 , 2008 , 2009 ] } } , { "Cast" : 0 } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999          } ]

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 > db . mediaCollection . find ( { $ or :    [ { Released : 1999 } , { Released : 2008 } , { Released : 2009 } ] } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Keanu Reeves" ,                          "Carry-Anne Moss" ,                          "Laurence Fishburne" ,                          "Hugo Weaving" ,                          "Gloria Foster" ,                          "Joe Pantoliano"                  ]          } ]

9. $nin

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 > db . mediaCollection . find ( { Type : "DVD" } , { "Cast" : 0 } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999          } ,          {                  "_id" : ObjectId ( "5354823fd85b463e729a2e58" ) ,                  "Type" : "DVD" ,                  "Title" : "Blade Runner" ,                  "Released" : 1982          } ,          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ] > db . mediaCollection . find ( { Released : { $ nin : [ 1999 , 2008 , 2009 ] } , Type : "DVD" } , { "Cast" : 0 } ) . toArray ( ) [          {                  "_id" : ObjectId ( "5354823fd85b463e729a2e58" ) ,                  "Type" : "DVD" ,                  "Title" : "Blade Runner" ,                  "Released" : 1982          } ,          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ]

10.  $all

与$in有点相似，只不过$all是所有属性要与文档匹配。$in只匹配其一就行。

1 2 > db . mediaCollection . find ( { Released : { $ all : [ "2010" , "2009" ] } } , { "Cast" : 0 } ) . toArray ( ) [ ]

11.  多个表达式

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 > db . mediaCollection . find ( { $ or : [ { "Title" : "Toy Story 3" } , { "ISBN" : "987-1-4302-3051-9" } ] } ) . toArray ( ) [          {                  "_id" : ObjectId ( "5353462f93efef02c962da71" ) ,                  "Type" : "Book" ,                  "Title" : "Definitive Guide to MongoDB, the" ,                  "ISBN" : "987-1-4302-3051-9" ,                  "Publisher" : "Apress" ,                  "Author" : [                          "Membrey, Peter" ,                          "Plugge, Eelco" ,                          "Hawkins, Tim"                  ]          } ,          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ]

1 2 3 4 5 6 7 8 9 > db . mediaCollection . find ( { "Type" : "DVD" , $ or : [ { "Title" : "Toy Story 3" } , { "ISBN" : "987-1-4302-3051-9" } ] } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ]

11. 切片

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 > db . mediaCollection . find ( { "Title" : "Matrix, The" } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Keanu Reeves" ,                          "Carry-Anne Moss" ,                          "Laurence Fishburne" ,                          "Hugo Weaving" ,                          "Gloria Foster" ,                          "Joe Pantoliano"                  ]          } ] > db . mediaCollection . find ( { "Title" : "Matrix, The" } , { "Cast" : { $ slice : 3 } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Keanu Reeves" ,                          "Carry-Anne Moss" ,                          "Laurence Fishburne"                  ]          } ] > db . mediaCollection . find ( { "Title" : "Matrix, The" } , { "Cast" : { $ slice : - 3 } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Hugo Weaving" ,                          "Gloria Foster" ,                          "Joe Pantoliano"                  ]          } ] > db . mediaCollection . find ( { "Title" : "Matrix, The" } , { "Cast" : { $ slice : [ 2 , 3 ] } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Laurence Fishburne" ,                          "Hugo Weaving" ,                          "Gloria Foster"                  ]          } ] > db . mediaCollection . find ( { "Title" : "Matrix, The" } , { "Cast" : { $ slice : [ - 5 , 4 ] } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Carry-Anne Moss" ,                          "Laurence Fishburne" ,                          "Hugo Weaving" ,                          "Gloria Foster"                  ]          } ]

12. $mod

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 > db . mediaCollection . find ( { Type : "DVD" , Released : { $ mod : [ 2 , 0 ] } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "5354823fd85b463e729a2e58" ) ,                  "Type" : "DVD" ,                  "Title" : "Blade Runner" ,                  "Released" : 1982          } ,          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ]   > db . mediaCollection . find ( { Type : "DVD" , Released : { $ mod : [ 2 , 1 ] } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Keanu Reeves" ,                          "Carry-Anne Moss" ,                          "Laurence Fishburne" ,                          "Hugo Weaving" ,                          "Gloria Foster" ,                          "Joe Pantoliano"                  ]          } ]

13. $size

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 > db . mediaCollection . find ( { Tracklist : { $ size : 2 } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "5353463193efef02c962da73" ) ,                  "Type" : "CD" ,                  "Artist" : "Nirvana" ,                  "Title" : "Nevermind" ,                  "Tracklist" : [                          {                                  "Track" : "1" ,                                  "Title" : "Smells like teen spirit" ,                                  "Length" : "5:02"                          } ,                          {                                  "Track" : "2" ,                                  "Title" : "In Bloom" ,                                  "Length" : "4:15"                          }                  ]          } ] > db . mediaCollection . find ( { Cast : { $ size : 1 } } ) . toArray ( ) [ ] > db . mediaCollection . find ( { Cast : { $ size : 6 } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Keanu Reeves" ,                          "Carry-Anne Moss" ,                          "Laurence Fishburne" ,                          "Hugo Weaving" ,                          "Gloria Foster" ,                          "Joe Pantoliano"                  ]          } ]

14. $exists

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 > db . mediaCollection . find ( { Author : { $ exists : true } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "5353462f93efef02c962da71" ) ,                  "Type" : "Book" ,                  "Title" : "Definitive Guide to MongoDB, the" ,                  "ISBN" : "987-1-4302-3051-9" ,                  "Publisher" : "Apress" ,                  "Author" : [                          "Membrey, Peter" ,                          "Plugge, Eelco" ,                          "Hawkins, Tim"                  ]          } ] > db . mediaCollection . find ( { Author : { $ exists : false } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "5353462f93efef02c962da72" ) ,                  "Type" : "CD" ,                  "Artist" : "Nirvana" ,                  "Title" : "Nevermind"          } ,          {                  "_id" : ObjectId ( "5353463193efef02c962da73" ) ,                  "Type" : "CD" ,                  "Artist" : "Nirvana" ,                  "Title" : "Nevermind" ,                  "Tracklist" : [                          {                                  "Track" : "1" ,                                  "Title" : "Smells like teen spirit" ,                                  "Length" : "5:02"                          } ,                          {                                  "Track" : "2" ,                                  "Title" : "In Bloom" ,                                  "Length" : "4:15"                          }                  ]          } ,          {                  "_id" : ObjectId ( "53548225d85b463e729a2e57" ) ,                  "Type" : "DVD" ,                  "Title" : "Matrix, The" ,                  "Released" : 1999 ,                  "Cast" : [                          "Keanu Reeves" ,                          "Carry-Anne Moss" ,                          "Laurence Fishburne" ,                          "Hugo Weaving" ,                          "Gloria Foster" ,                          "Joe Pantoliano"                  ]          } ,          {                  "_id" : ObjectId ( "5354823fd85b463e729a2e58" ) ,                  "Type" : "DVD" ,                  "Title" : "Blade Runner" ,                  "Released" : 1982          } ,          {                  "_id" : ObjectId ( "53548254d85b463e729a2e59" ) ,                  "Type" : "DVD" ,                  "Title" : "Toy Story 3" ,                  "Released" : 2010          } ]

15. $type

根据BSON类型来检索集合中匹配的结果。

MongoDB中可以使用的类型如下表所示：

类型描述类型值Double1String2Object3Array4Binary data5Object id7Boolean8Date9Null10Regular expression11JavaScript code13Symbol14JavaScript code with scope1532-bit integer16Timestamp1764-bit integer18Min key255Max key127

下面这个实例是查询嵌入对象。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 > db . mediaCollection . find ( { Tracklist : { $ type : 3 } } ) . toArray ( ) [          {                  "_id" : ObjectId ( "5353463193efef02c962da73" ) ,                  "Type" : "CD" ,                  "Artist" : "Nirvana" ,                  "Title" : "Nevermind" ,                  "Tracklist" : [                          {                                  "Track" : "1" ,                                  "Title" : "Smells like teen spirit" ,                                  "Length" : "5:02"                          } ,                          {                                  "Track" : "2" ,                                  "Title" : "In Bloom" ,                                  "Length" : "4:15"                          }                  ]          } ]