mongodb 数组字段的查询

匹配任何一个数组元素

任何一个数组元素匹配上给定的查询条件,当前文档就被选进结果集。查询文档:{ <array_field>: <value> }。实例:

1
2
3
4
5
6
7
> db.books.insert({ _id:1000, authors:["Eric","Andrew","Pony","Jackson"] })
WriteResult({ "nInserted" : 1 })
> db.books.find()
{ "_id" : 1000, "authors" : [ "Eric", "Andrew", "Pony", "Jackson" ] }
>
> db.books.find({ "authors": "Pony" })
{ "_id" : 1000, "authors" : [ "Eric", "Andrew", "Pony", "Jackson" ] }

精确匹配整个数组

所有数组元素,包括元素顺序,完全一样才算匹配成功。查询文档:{ <array_field>: [ <value1>,<value2>,<value3>,... }。实例:

1
2
3
4
> db.books.find({ "authors": ["Eric","Andrew"]}) // no result
> db.books.find({ "authors": [ "Eric", "Andrew", "Pony", "Jackson" ]})
{ "_id" : 1000, "authors" : [ "Eric", "Andrew", "Pony", "Jackson" ] }
> db.books.find({ "authors": [ "Eric", "Andrew", "Jackson", "Pony" ]}) // no result

如果数组元素不是简单类型,而是文档,那相应地,value 也得是文档。

匹配数组元素文档的字段

还可以使用圆点符号(.)匹配元素文档的字段,实例:

1
2
3
4
5
6
7
> db.books.remove({})
> db.books.insert({ "_id":1000, "authors" : [ { "id" : "1", "name" : "simon" }, { "id" : "2", "name" : "kingkong" }, { "id" : "3", "name" : "lily" } ] })
> db.books.insert({ "_id":1001, "authors" : [ { "id" : "3", "name" : "fowler" }, { "id" : "1", "name" : "simon" } ] })
> db.books.insert({ "_id":1002, "authors" : [ { "id" : "4", "name" : "Peter" }, { "id" : "1", "name" : "simon" } ] })
>
> db.books.find({ "authors.name": "Peter" })
{ "_id" : 1002, "authors" : [ { "id" : "4", "name" : "Peter" }, { "id" : "1", "name" : "simon" } ] }

匹配指定位置的数组元素

还可以在查询条件中指定元素的位置:{ <array_field>.<element_index>: <value> }。实例:

1
2
3
4
5
> db.books.find({ "authors.1": { "id":"2", "name":"kingkong" } })
{ "_id" : 1000, "authors" : [ { "id" : "1", "name" : "simon" }, { "id" : "2", "name" : "kingkong" }, { "id" : "3", "name" : "lily" } ] }
>
> db.books.find({ "authors.1.name": "kingkong" })
{ "_id" : 1000, "authors" : [ { "id" : "1", "name" : "simon" }, { "id" : "2", "name" : "kingkong" }, { "id" : "3", "name" : "lily" } ] }

单个数组元素同时满足多个条件

只要有一个数组元素满足通过 $elemMatch 操作符指定的多个条件,当前文档就被选进结果集。实例:

1
2
3
> db.books.find({ authors: { $elemMatch: { "id":"3","name":"lily" } } })
{ "_id" : 1000, "authors" : [ { "id" : "1", "name" : "simon" }, { "id" : "2", "name" : "kingkong" }, { "id" : "3", "name" : "lily" } ] }
>

当然只有一个查询条件也可以使用 $elemMatch,它等价于前面讲述的简单查询形式。

多个元素组合起来满足多个条件

首先,单个元素满足所有条件的记录肯定会被选进结果集。其实,一个元素满足条件 a,另一个满足条件 b,再一个满足条件 c,这三个组合起来满足了所有条件,这样的记录也会被选进结果集。查询文档:{ "<array_field>.<elem_field1>": <condition_a>, "<array_field>.<elem_field2>": <condition_b>,... }。实例:

1
2
3
4
5
6
7
8
9
10
11
12
> db.books.insert({ "_id":1003, "authors" : [ { "id" : "3", "name" : "unknown" }, { "id" : "5", "name" : "fowler" } ] })
>
> db.books.find()
{ "_id" : 1000, "authors" : [ { "id" : "1", "name" : "simon" }, { "id" : "2", "name" : "kingkong" }, { "id" : "3", "name" : "lily" } ] }
{ "_id" : 1001, "authors" : [ { "id" : "3", "name" : "fowler" }, { "id" : "1", "name" : "simon" } ] }
{ "_id" : 1002, "authors" : [ { "id" : "4", "name" : "Peter" }, { "id" : "1", "name" : "simon" } ] }
{ "_id" : 1003, "authors" : [ { "id" : "3", "name" : "unknown" }, { "id" : "5", "name" : "fowler" } ] }
>
> db.books.find({ "authors.id":"3","authors.name":"fowler" })
{ "_id" : 1001, "authors" : [ { "id" : "3", "name" : "fowler" }, { "id" : "1", "name" : "simon" } ] }
{ "_id" : 1003, "authors" : [ { "id" : "3", "name" : "unknown" }, { "id" : "5", "name" : "fowler" } ] }
>

实例数据只为了说明用法,没有太注意业务逻辑的合理性。

参考资源

Query Operator Array
Query on Arrays

0%