Mongodb聚合管道项目

Question

大家好，

我需要一些帮助来让聚合管道工作，我已经尝试了一段时间，但只做了一半。这是我的设置

用户文档如下：

{
"_id": { "$oid": "5fea4f976ca46d93c010d33d" },
.....................
"firstname": "firstname1",
"lastname": "lastname1",
"key1": "value1",
"key2": "value2",
"key3": "value3",
"key4": "value4",
..................
"certifications": {
certs: [
{
"_id": "5fea44b96ca46d93c010d330",
"itemID": 1,
"date_certified": "2011-05-30 05:09:48"
},
{
"_id": "5fea44b96ca46d93c010d334",
"itemID": 3,
"date_certified": "2007-01-30 06:01:51"
},
{
"_id": "5fea44b96ca46d93c010d337",
"itemID": 5,
"date_certified": "2007-09-21 16:52:52"
}
]
},
.......................
...................
.....................
},
{
"_id": { "$oid": "5fea4f976ca46d93c010545453" },
.....................
.....................
"firstname": "firstname2",
"lastname": "lastname2",
"key1": "value1",
"key2": "value2",
"key3": "value3",
"key4": "value4",
..................
"certifications": {
certs: [
{
"_id": "5fea44b96ca46d93c010d330",
"itemID": 2,
"date_certified": "2011-05-30 05:09:48"
},
{
"_id": "5fea44b96ca46d93c010d334",
"itemID": 4,
"date_certified": "2007-01-30 06:01:51"
}
]
},
.......................
...................
.....................
}

认证文档是后续

[{
"_id": {
"$oid": "5fea44b96ca46d93c010d32e"
},
"itemID": 1,
"acronym": "acro1",
"country": "my Great Country",
"name": {
"key1": "My Key 1 description",
"key2": "My Key 2 description",
"key3": "My Key 3 description",
"key4": "My Key 4 description"
},
"website": "www.myGreatWebsite.com"
},
{
"_id": {
"$oid": "5fea44b96ca46d93c010d32f"
},
"itemID": 2,
"acronym": "acro2",
"country": "my Great Country",
"name": {
"key1": "My Key 1 description",
"key2": "My Key 2 description",
"key3": "My Key 3 description",
"key4": "My Key 4 description"
},
"website": "www.myGreatWebsite.com"
},
{
"_id": {
"$oid": "5fea44b96ca46d93c010d330"
},
"itemID": 3,
"country": "my Great Country",
"name": {
"key1": "My Key 1 description",
"key2": "My Key 2 description",
"key3": "My Key 3 description",
"key4": "My Key 4 description"
},
"website": "www.myGreatWebsite.com"
},
{
"_id": {
"$oid": "5fea44b96ca46d93c010d331"
},
"itemID": 4,
"country": "my Great Country",
"name": {
"key1": "My Key 1 description",
"key2": "My Key 2 description",
"key3": "My Key 3 description",
"key4": "My Key 4 description"
},
"website": "www.myGreatWebsite.com"
},
{
"_id": {
"$oid": "5fea44b96ca46d93c010d332"
},
"itemID": 5,
"country": "my Great Country",
"name": {
"key1": "My Key 1 description",
"key2": "My Key 2 description",
"key3": "My Key 3 description",
"key4": "My Key 4 description"
},
"website": "www.myGreatWebsite.com"
}]

现在我的聚合

db.users.aggregate([
{
'$lookup': {
'from': 'certifications', 
'let': {
'certs': '$certifications.certs'
}, 
'pipeline': [
{
'$match': {
'$expr': {
'$in': [
'$itemID', '$$certs.itemID'
]
}
}
}, {
'$project': {
'_id': 0, 
'itemID': '$itemID', 
'description': '$name.key1', 
'acronyme': '$acronym', 
'country': '$country', 
'count': {
'$size': '$$certs'
}, 
'date_cert': '$$certs.date_certified'
}
}
], 
'as': 'certifications'
}
}
])

结果是

{
.........
...........
"firstname": "firstname1",
"lastname": "lastname1",
"key1": "value1",
"key2": "value2",
"key3": "value3",
"key4": "value4",
..................
certification: {
certs: [
{
itemID: 1,
country: 'my Great Country',
description: 'My Key 1 description',
count: 1,
date_cert: [
0:"2005-01-06 10:48:16"
]
},
]
}
........
.........
},
{
.........
...........
"firstname": "firstname2",
"lastname": "lastname2",
"key1": "value1",
"key2": "value2",
"key3": "value3",
"key4": "value4",
..................
certification: {
certs: [
{
itemID: 2,
country: 'my Great Country',
description: 'My Key 2 description',
count: 2,
date_cert: [
"2005-01-06 10:48:16"
"2014-06-21 22:44:56"
]
},
{
itemID: 4,
country: 'my Great Country',
description: 'My Key 4 description',
count: 2,
date_cert: [
"2005-01-06 10:48:16"
"2014-06-21 22:44:56"
]
}
]
}
........
.........
}

但是预期的结果是

{
.........
...........
"firstname": "firstname1",
"lastname": "lastname1",
"key1": "value1",
"key2": "value2",
"key3": "value3",
"key4": "value4",
..................
certification: {
certs: [
{
itemID: 1,
country: 'my Great Country',
description: 'My Key 1 description',
date_cert: "2005-01-06 10:48:16"
},
],
count: 1,
}
........
.........
},
{
.........
...........
"firstname": "firstname2",
"lastname": "lastname2",
"key1": "value1",
"key2": "value2",
"key3": "value3",
"key4": "value4",
..................
certification: {
certs: [
{
itemID: 2,
country: 'my Great Country',
description: 'My Key 2 description',
date_cert: "2005-01-06 10:48:16"
},
{
itemID: 4,
country: 'my Great Country'
description: 'My Key 4 description',
date_cert: "2014-06-21 22:44:56"
}
],
count: 2
}
........
.........
}

基本上，我希望将管道匹配阶段的结果与证书查找的文档合并，这样我就可以同时使用两者。任何指针在公式化聚合时都将受到极大的赞赏。

谢谢

Answer 1

• $unwind解构certs数组
• $lookup和certifications通过certifications.certs.itemID作为localField，itemID作为foreignField，结果为certification
• $unwind解构来自查找和certification中的结果
• $group通过_id，重建certs数组，并使用$mergeObjects合并后推送您需要的字段，使用$sum获取计数

db.users.aggregate([
{ $unwind: "$certifications.certs" },
{
$lookup: {
from: "certifications",
localField: "certifications.certs.itemID",
foreignField: "itemID",
as: "certification"
}
},
{ $unwind: "$certification" },
{
$group: {
_id: "$_id",
certs: {
$push: {
$mergeObjects: [
"$certifications.certs",
{
country: "$certification.country",
description: "$certification.name.key1"
}
]
}
},
count: { $sum: 1 }
}
}
])

游乐场

---

第二种方法没有$unroll和$group，这可能会导致性能和速度问题，

• $lookup和certifications集合
• $map迭代certifications.certs数组的循环
• $reduce迭代certification的循环、检查itemID的条件并返回特定字段
• 返回到$map，使用$mergeObjects合并当前对象并从$reduce返回对象
• 使用$size获取certs的计数`

db.users.aggregate([
{
$lookup: {
from: "certifications",
localField: "certifications.certs.itemID",
foreignField: "itemID",
as: "certification"
}
},
{
$addFields: {
"certification": "$$REMOVE",
"certifications.certs": {
$map: {
input: "$certifications.certs",
as: "c",
in: {
$mergeObjects: [
"$$c",
{
$reduce: {
input: "$certification",
initialValue: {},
in: {
$cond: [
{ $eq: ["$$this.itemID", "$$c.itemID"] },
{
country: "$$this.country",
description: "$$this.name.key1"
},
"$$value"
]
}
}
}
]
}
}
},
"certifications.count": { $size: "$certifications.certs" }
}
}
])

游乐场