使用$elemMatch与不使用$elemMatch区别
(1)单个数组
db.orders.insertMany([
{ _id:1, name:'zhangsan', score:[20,35,40,60] },
{ _id:2, name:'liuchen', score:[15,67,86,100] },
{ _id:3, name:'wangfeilin', score:[26,29,50,68] },
{ _id:4, name:'zhihaimei', score:[7,15,22,30] }
])
db.orders.find()
{ "_id" : 1, "name" : "zhangsan", "score" : [ 20, 35, 40, 60 ] }
{ "_id" : 2, "name" : "liuchen", "score" : [ 15, 67, 86, 100 ] }
{ "_id" : 3, "name" : "wangfeilin", "score" : [ 26, 29, 50, 68 ] }
{ "_id" : 4, "name" : "zhihaimei", "score" : [ 7, 15, 22, 30 ] }
db.orders.find({ score:15 })
{ "_id" : 2, "name" : "liuchen", "score" : [ 15, 67, 86, 100 ] }
{ "_id" : 4, "name" : "zhihaimei", "score" : [ 7, 15, 22, 30 ] }
db.orders.find({ 'score.1':15 })
{ "_id" : 4, "name" : "zhihaimei", "score" : [ 7, 15, 22, 30 ] }
---不等价
db.orders.find({ score:{ $gt:15, $lte:50 } }) ---类似score>15 and score<50,只要数组有一个值在这个范围内则匹配
{ "_id" : 1, "name" : "zhangsan", "score" : [ 20, 35, 40, 60 ] }
{ "_id" : 2, "name" : "liuchen", "score" : [ 15, 67, 86, 100 ] }
{ "_id" : 3, "name" : "wangfeilin", "score" : [ 26, 29, 50, 68 ] }
{ "_id" : 4, "name" : "zhihaimei", "score" : [ 7, 15, 22, 30 ] }
db.orders.find({ score:{ $elemMatch:{ $gt:15, $lte:50 } } }) ---两个条件需要同时满足,只要数组有一个值这两个条件则匹配
{ "_id" : 1, "name" : "zhangsan", "score" : [ 20, 35, 40, 60 ] }
{ "_id" : 3, "name" : "wangfeilin", "score" : [ 26, 29, 50, 68 ] }
{ "_id" : 4, "name" : "zhihaimei", "score" : [ 7, 15, 22, 30 ] }
---等价
db.orders.find({ score:{ $gt:80 } })
{ "_id" : 2, "name" : "liuchen", "score" : [ 15, 67, 86, 100 ] }
db.orders.find({ score:{ $elemMatch:{ $gt:80 } }})
{ "_id" : 2, "name" : "liuchen", "score" : [ 15, 67, 86, 100 ] }
(2)嵌套数组
db.survey.insertMany( [
{ "_id": 1, "results": [ { "product": "abc", "score": 10 },
{ "product": "xyz", "score": 5 } ] },
{ "_id": 2, "results": [ { "product": "abc", "score": 8 },
{ "product": "xyz", "score": 7 } ] },
{ "_id": 3, "results": [ { "product": "abc", "score": 7 },
{ "product": "xyz", "score": 8 } ] },
{ "_id": 4, "results": [ { "product": "abc", "score": 7 },
{ "product": "def", "score": 8 } ] },
{ "_id": 5, "results": { "product": "xyz", "score": 7 } }
] )
db.survey.find()
{ "_id" : 1, "results" : [ { "product" : "abc", "score" : 10 }, { "product" : "xyz", "score" : 5 } ] }
{ "_id" : 2, "results" : [ { "product" : "abc", "score" : 8 }, { "product" : "xyz", "score" : 7 } ] }
{ "_id" : 3, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "xyz", "score" : 8 } ] }
{ "_id" : 4, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "def", "score" : 8 } ] }
{ "_id" : 5, "results" : { "product" : "xyz", "score" : 7 } }
db.survey.find({ 'results.product':'xyz' })
{ "_id" : 1, "results" : [ { "product" : "abc", "score" : 10 }, { "product" : "xyz", "score" : 5 } ] }
{ "_id" : 2, "results" : [ { "product" : "abc", "score" : 8 }, { "product" : "xyz", "score" : 7 } ] }
{ "_id" : 3, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "xyz", "score" : 8 } ] }
{ "_id" : 5, "results" : { "product" : "xyz", "score" : 7 } }
---嵌套文档使用$elemMatch会忽略非数组文档,如:"_id" : 5这行文档并不是数组,也就是说$elemMatch用于数组过滤,忽略非数组文档
db.survey.find({ 'results':{ $elemMatch:{ product:'xyz' } } })
{ "_id" : 1, "results" : [ { "product" : "abc", "score" : 10 }, { "product" : "xyz", "score" : 5 } ] }
{ "_id" : 2, "results" : [ { "product" : "abc", "score" : 8 }, { "product" : "xyz", "score" : 7 } ] }
{ "_id" : 3, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "xyz", "score" : 8 } ] }
db.survey.find({ 'results.product':{ $ne:'xyz' } })
{ "_id" : 4, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "def", "score" : 8 } ] }
---嵌套文档使用$elemMatch会忽略非数组文档,如:"_id" : 5这行文档并不是数组,也就是说$elemMatch用于数组
db.survey.find({ 'results':{ $elemMatch:{ product:{ $ne:'xyz' } } } })
{ "_id" : 1, "results" : [ { "product" : "abc", "score" : 10 }, { "product" : "xyz", "score" : 5 } ] }
{ "_id" : 2, "results" : [ { "product" : "abc", "score" : 8 }, { "product" : "xyz", "score" : 7 } ] }
{ "_id" : 3, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "xyz", "score" : 8 } ] }
{ "_id" : 4, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "def", "score" : 8 } ] }
db.survey.find({ 'results.score':{ $gte:8 } })
{ "_id" : 1, "results" : [ { "product" : "abc", "score" : 10 }, { "product" : "xyz", "score" : 5 } ] }
{ "_id" : 2, "results" : [ { "product" : "abc", "score" : 8 }, { "product" : "xyz", "score" : 7 } ] }
{ "_id" : 3, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "xyz", "score" : 8 } ] }
{ "_id" : 4, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "def", "score" : 8 } ] }
db.survey.find({ 'results':{ $elemMatch:{ score: { $gte:8 } } } })
{ "_id" : 1, "results" : [ { "product" : "abc", "score" : 10 }, { "product" : "xyz", "score" : 5 } ] }
{ "_id" : 2, "results" : [ { "product" : "abc", "score" : 8 }, { "product" : "xyz", "score" : 7 } ] }
{ "_id" : 3, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "xyz", "score" : 8 } ] }
{ "_id" : 4, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "def", "score" : 8 } ] }
db.survey.find({ 'results':{ $elemMatch:{ product:'xyz', score:8 } } })
{ "_id" : 3, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "xyz", "score" : 8 } ] }
db.survey.find({ 'results':{ $elemMatch:{ score:8, product:'xyz' } } })
{ "_id" : 3, "results" : [ { "product" : "abc", "score" : 7 }, { "product" : "xyz", "score" : 8 } ] }
另外使用$elemMatch不可使用results.score表示
db.survey.find({ 'results.score':{ score:{ $elemMatch:{ $gt:5 } } } })