MongoDB查询数组

在学习MongDB，在此做下学习记录

首先插入db

> db.food.insert({"_id":1, "fruit":["apple","banana","peach"]})
> db.food.insert({"_id":2,"fruit":["apple","kumquat","orange"]})
> db.food.insert({"_id":3,"fruit":["cherry","banana","apple"]})

1.查询单个元素

> db.food.find({"fruit":"apple"})
{ "_id" : 1, "fruit" : [ "apple", "banana", "peach" ] }
{ "_id" : 2, "fruit" : [ "apple", "kumquat", "orange" ] }
{ "_id" : 3, "fruit" : [ "cherry", "banana", "apple" ] }

2.$all同时匹配多个元素

> db.food.find({"fruit":{$all:["apple","orange"]}})
{ "_id" : 2, "fruit" : [ "apple", "kumquat", "orange" ] }

3.精确匹配

查找只包含"apple","banana","peach"数组

> db.food.find({"fruit":["apple","banana","peach"]})
{ "_id" : 1, "fruit" : [ "apple", "banana", "peach" ] }

4.查找数组特定位置的元素

> db.food.find({"fruit.2":"peach"})
{ "_id" : 1, "fruit" : [ "apple", "banana", "peach" ] }

5.查找特定长度的数组

这时应该用$SIZE，SIZE不能和其他查询条件一起使用（如$gt）

> db.food.update({"_id":1},{"$push":{"fruit":"leo"}})
> db.food.find()
{ "_id" : 1, "fruit" : [ "apple", "banana", "peach", "leo" ] }
{ "_id" : 2, "fruit" : [ "apple", "kumquat", "orange" ] }
{ "_id" : 3, "fruit" : [ "cherry", "banana", "apple" ] }
> db.food.find({"fruit":{"$size":3}})
{ "_id" : 2, "fruit" : [ "apple", "kumquat", "orange" ] }
{ "_id" : 3, "fruit" : [ "cherry", "banana", "apple" ] }

6.匹配子集$slice，slice默认返回文档的所有的键

> db.food.find({"_id":1},{"fruit":{"$slice":2}})
{ "_id" : 1, "fruit" : [ "apple", "banana" ] }
> db.food.find({"_id":1},{"fruit":{"$slice":-1}})
{ "_id" : 1, "fruit" : [ "leo" ] }
> db.food.find({"_id":1},{"fruit":{"$slice":[2,3]}})
{ "_id" : 1, "fruit" : [ "peach", "leo" ] }

7.返回一个匹配的数组元素

将"_id"为1记录修改下，加入“detail”

> db.food.update({"_id":1},{"$push":{"detail":{"buy":"leo","price":20}}})
> db.food.update({"_id":1},{"$push":{"detail":{"buy":"leo","price":30}}})

然后查找包含“leo”的记录

> db.food.find({"detail.buy":"leo"},{"detail.$":1})
{ "_id" : 1, "detail" : [ { "buy" : "leo", "price" : 20 } ] }

或者

{ "_id" : 1, "detail" : [ { "buy" : "leo", "price" : 20 } ] }
> db.food.find({"detail.buy":"leo"})
{ "_id" : 1, "fruit" : [ "apple", "banana", "peach", "leo" ], "detail" : [ { "buy" : "leio", "price" : 10 }, { "buy" : "leo", "price" : 20 }, { "buy" : "leo", "price" : 30 } ] }

8.数组和范围查询相互作用

假如现在有一个集合

> db.arr.find()
{ "_id" : ObjectId("5479b6c3d7b8886a626330d9"), "x" : 5 }
{ "_id" : ObjectId("5479b6c6d7b8886a626330da"), "x" : 10 }
{ "_id" : ObjectId("5479b6c8d7b8886a626330db"), "x" : 15 }
{ "_id" : ObjectId("5479b6cbd7b8886a626330dc"), "x" : 25 }
{ "_id" : ObjectId("5479b763d7b8886a626330dd"), "x" : [ 10, 25 ] }

如果想查找x位于10到20之间的文档

> db.arr.find({"x":{"$gt":10,"$lt":20}})
{ "_id" : ObjectId("5479b6c8d7b8886a626330db"), "x" : 15 }
{ "_id" : ObjectId("5479b763d7b8886a626330dd"), "x" : [ 10, 25 ] }

可以看到数组[10,25]也符合查询条件

可以用$elemMatch进行数组范围匹配，但是$elemMatch不会匹配非数组元素

> db.arr.find({"x":{"$elemMatch":{"$gte":10,"$lte":25}}})
{ "_id" : ObjectId("5479b763d7b8886a626330dd"), "x" : [ 10, 25 ] }

如果在x字段上建立了索引，可以使用min()和max()

db.arr.find({"x":{"$elemMatch":{"$gte":10,"$lte":25}}}).min({"x":10}).max({"x":25})
{"x":15}