嵌套数组的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"
- group:名称-"group1"
- 联系人:localId数组-["c1"，"c3"，不存在的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更新
这样一个好的问题应该得到现代的回应.实际上，可以在现代MongoDB 3.2版中通过简单地 $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屋！
