嵌套数组的 MongoDB 投影 [英] MongoDB Projection of Nested Arrays
问题描述
我有一个帐户"集合，其中包含与此结构类似的文档:
I've got a collection "accounts" which contains documents similar to this structure:
{
"email" : "john.doe@acme.com",
"groups" : [
{
"name" : "group1",
"contacts" : [
{ "localId" : "c1", "address" : "some address 1" },
{ "localId" : "c2", "address" : "some address 2" },
{ "localId" : "c3", "address" : "some address 3" }
]
},
{
"name" : "group2",
"contacts" : [
{ "localId" : "c1", "address" : "some address 1" },
{ "localId" : "c3", "address" : "some address 3" }
]
}
]
}
通过
q = { "email" : "john.doe@acme.com", "groups" : { $elemMatch: { "name" : "group1" } } }
p = { "groups.name" : 0, "groups" : { $elemMatch: { "name" : "group1" } } }
db.accounts.find( q, p ).pretty()
我将成功获取我感兴趣的指定帐户的组.
I'll successfully get just the group of a specified account I'm interested in.
问题:如何获取指定帐户"的某个组"内的联系人"的有限列表?假设我有以下参数:
Question: How can I get a limited list of "contacts" within a certain "group" of a specified "account"? Let's suppose I've got the following arguments:
- 帐户:电子邮件 - john.doe@acme.com"
- 组:名称 - group1"
- contact:localId 数组 - [ "c1", "c3", "Not existing id" ]
鉴于这些论点，我希望得到以下结果:
Given these arguments I'd like to have the following result:
{
"groups" : [
{
"name" : "group1", (might be omitted)
"contacts" : [
{ "localId" : "c1", "address" : "some address 1" },
{ "localId" : "c3", "address" : "some address 3" }
]
}
]
}
除了由此产生的联系人之外，我不需要任何其他东西.
I don't need anything else apart from the resulting contacts.
方法
为了简单起见，所有查询都尝试仅获取一个匹配的联系人而不是匹配的联系人列表.我尝试了以下查询但没有成功:
All queries try to fetch just one matching contact instead of a list of matching contacts, for the sake of simplicity. I've tried the following queries without any success:
p = { "groups.name" : 0, "groups" : { $elemMatch: { "name" : "group1", "contacts" : { $elemMatch: { "localId" : "c1" } } } } }
p = { "groups.name" : 0, "groups" : { $elemMatch: { "name" : "group1", "contacts.localId" : "c1" } } }
not working: returns whole array or nothing depending on localId
p = { "groups.$" : { $elemMatch: { "localId" : "c1" } } }
error: {
"$err" : "Can't canonicalize query: BadValue Cannot use $elemMatch projection on a nested field.",
"code" : 17287
}
p = { "groups.contacts" : { $elemMatch: { "localId" : "c1" } } }
error: {
"$err" : "Can't canonicalize query: BadValue Cannot use $elemMatch projection on a nested field.",
"code" : 17287
}
感谢任何帮助！
推荐答案
2017 更新
这么好的问题值得现代的回答.请求的那种数组过滤实际上可以通过简单的 在 3.2 后的现代 MongoDB 版本中完成$match 和 $project 管道阶段，很像原始的普通查询操作.
2017 Update
Such a well put question deserves a modern response. The sort of array filtering requested can actually be done in modern MongoDB releases post 3.2 via simply $match and $project pipeline stages, much like the original plain query operation intends.
db.accounts.aggregate([
{ "$match": {
"email" : "john.doe@acme.com",
"groups": {
"$elemMatch": {
"name": "group1",
"contacts.localId": { "$in": [ "c1","c3", null ] }
}
}
}},
{ "$addFields": {
"groups": {
"$filter": {
"input": {
"$map": {
"input": "$groups",
"as": "g",
"in": {
"name": "$$g.name",
"contacts": {
"$filter": {
"input": "$$g.contacts",
"as": "c",
"cond": {
"$or": [
{ "$eq": [ "$$c.localId", "c1" ] },
{ "$eq": [ "$$c.localId", "c3" ] }
]
}
}
}
}
}
},
"as": "g",
"cond": {
"$and": [
{ "$eq": [ "$$g.name", "group1" ] },
{ "$gt": [ { "$size": "$$g.contacts" }, 0 ] }
]
}
}
}
}}
])
这利用了$filter 和 $map运算符只返回满足条件的数组元素，并且性能比使用 $unwind.由于流水线阶段有效地反映了 .find() 操作中查询"和项目"的结构，因此这里的性能基本上与此类和操作相当.
This makes use of of the $filter and $map operators to only return the elements from the arrays as would meet the conditions, and is far better for performance than using $unwind. Since the pipeline stages effectively mirror the structure of "query" and "project" from a .find() operation, the performance here is basically on par with such and operation.
请注意，如果目的是实际跨文档"，将多个"文档而不是一个"文档中的细节组合在一起，那么这通常需要某种类型的 $unwind 操作以便这样做，因此可以访问数组项以进行分组".
Note that where the intention is to actually work "across documents" to bring details together out of "multiple" documents rather than "one", then this would usually require some type of $unwind operation in order to do so, as such enabling the array items to be accessible for "grouping".
这基本上是方法:
db.accounts.aggregate([
// Match the documents by query
{ "$match": {
"email" : "john.doe@acme.com",
"groups.name": "group1",
"groups.contacts.localId": { "$in": [ "c1","c3", null ] },
}},
// De-normalize nested array
{ "$unwind": "$groups" },
{ "$unwind": "$groups.contacts" },
// Filter the actual array elements as desired
{ "$match": {
"groups.name": "group1",
"groups.contacts.localId": { "$in": [ "c1","c3", null ] },
}},
// Group the intermediate result.
{ "$group": {
"_id": { "email": "$email", "name": "$groups.name" },
"contacts": { "$push": "$groups.contacts" }
}},
// Group the final result
{ "$group": {
"_id": "$_id.email",
"groups": { "$push": {
"name": "$_id.name",
"contacts": "$contacts"
}}
}}
])
这是对多个匹配项的数组过滤"，.find() 的基本投影功能无法做到这一点.
This is "array filtering" on more than a single match which the basic projection capabilities of .find() cannot do.
您有嵌套"数组，因此您需要处理$unwind 两次.与其他操作一起.
You have "nested" arrays therefore you need to process $unwind twice. Along with the other operations.
这篇关于嵌套数组的 MongoDB 投影的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
