mongodb:复杂的嵌套聚合

Question

我有这个集合：

[
{
"_id": {
"$oid": "60b22e1dbd46fa18a8308318"
},
"title": "basketball",
"price": 12,
"category": "Furniture",
"description": "",
"images": [
"http://res.cloudinary.com/hadarush100/image/upload/v1622289949/nfg948x3zro6gbiuknrz.jpg"
],
"categoryId": 1,
"userId": "60ad16493062eb11141d4927",
"createdAt": 1622289948232,
"chats": [
{
"id": 1,
"createdAt": 1622289948232,
"messages": [
{
"id": "1",
"createdAt": 1622289948232,
"senderId": "60ad16493062eb11141d4927",
"text": "Hello, Im the seller of this product."
}
]
},
{
"id": "2",
"createdAt": 1622289948232,
"messages": [
{
"id": 1,
"createdAt": 1622289948232,
"senderId": "60ad16493062eb11141d4927",
"text": "Hello, Im the seller of this product."
}
]
}
]
}
]

我想找到特定的文档(通过_id(，然后深入到该文档中的特定聊天(通过id(，而不是使用$lookup来替换"；发送方Id"；每个消息中的属性；发送者"；属性，该属性包含另一个集合(用户(中存在的完整发件人详细信息(作为用户(。结果需要看起来像这样：

[
{
"_id": {
"$oid": "60b22e1dbd46fa18a8308318"
},
"title": "basketball",
"price": 12,
"category": "Furniture",
"description": "",
"images": [
"http://res.cloudinary.com/hadarush100/image/upload/v1622289949/nfg948x3zro6gbiuknrz.jpg"
],
"categoryId": 1,
"userId": "60ad16493062eb11141d4927",
"createdAt": 1622289948232,
"chats": [
{
"id": 1,
"createdAt": 1622289948232,
"messages": [
{
"id": "1",
"createdAt": 1622289948232,
"sender": {
"_id": {
"$oid": "60ad16493062eb11141d4927"
},
"username": "hadar",
"email": "[e-mail]",
"profileImgUrl": "https://randomuser.me/api/portraits/men/79.jpg",
"createdAt": 1621956168518
},
"text": "Hello, Im the seller of this product."
}
]
},
{
"id": "2",
"createdAt": 1622289948232,
"messages": [
{
"id": 1,
"createdAt": 1622289948232,
"sender": {
"_id": {
"$oid": "60ad16493062eb11141d4927"
},
"username": "hadar",
"email": "[e-mail]",
"profileImgUrl": "https://randomuser.me/api/portraits/men/79.jpg",
"createdAt": 1621956168518
},
"text": "Hello, Im the seller of this product."
}
]
}
]
}
]

Answer 1

您可以使用以下聚合：

• $match仅筛选所选文档(_id(
• $unwind多次将数组转换为对象
• $lookup查询外部集合(用户(
• 按相反顺序排列的$group

我假设你的收藏或多或少是这样的(下次，发布两个收藏，并在工作操场上发布一个例子(

db={
"products": [
{
"_id": {
"$oid": "60b22e1dbd46fa18a8308318"
},
"title": "basketball",
"price": 12,
"category": "Furniture",
"description": "",
"images": [
"http://res.cloudinary.com/hadarush100/image/upload/v1622289949/nfg948x3zro6gbiuknrz.jpg"
],
"categoryId": 1,
"userId": "60ad16493062eb11141d4927",
"createdAt": 1622289948232,
"chats": [
{
"id": 1,
"createdAt": 1622289948232,
"messages": [
{
"id": "1",
"createdAt": 1622289948232,
"senderId": "60ad16493062eb11141d4927",
"text": "Hello, Im the seller of this product."
}
]
},
{
"id": "2",
"createdAt": 1622289948232,
"messages": [
{
"id": 1,
"createdAt": 1622289948232,
"senderId": "60ad16493062eb11141d4927",
"text": "Hello, Im the seller of this product."
}
]
}
]
},
{
"_id": {
"$oid": "60b22e1dbd46fa18a8308319"
},
"title": "volleyball",
"price": 8,
"category": "Furniture",
"description": "",
"images": [
"http://res.cloudinary.com/hadarush100/image/upload/v1622289949/nfg948x3zro6gbiuknrz.jpg"
],
"categoryId": 1,
"userId": "60ad16493062eb11141d4927",
"createdAt": 1622289948232,
"chats": [
{
"id": 1,
"createdAt": 1622289948232,
"messages": [
{
"id": "1",
"createdAt": 1622289948232,
"senderId": "60ad16493062eb11141d4927",
"text": "Hello, Im the seller of this product."
}
]
},
{
"id": "2",
"createdAt": 1622289948232,
"messages": [
{
"id": 1,
"createdAt": 1622289948232,
"senderId": "60ad16493062eb11141d4928",
"text": "Hello, Im the seller of this product."
}
]
}
]
}
],
"users": [
{
"_id": {
"$oid": "60ad16493062eb11141d4927"
},
"username": "hadar",
"email": "[e-mail]",
"profileImgUrl": "https://randomuser.me/api/portraits/men/79.jpg",
"createdAt": 1621956168518
},
{
"_id": {
"$oid": "60ad16493062eb11141d4928"
},
"username": "test",
"email": "[e-mail]",
"profileImgUrl": "https://randomuser.me/api/portraits/men/49.jpg",
"createdAt": 1621956168528
},

]
}

这里是工作聚合：

db.products.aggregate([
{
"$match": {
"_id": {
"$oid": "60b22e1dbd46fa18a8308319"
}
}
},
{
"$unwind": "$chats"
},
{
"$unwind": "$chats.messages"
},
{
"$addFields": {
"chats.messages.senderIdObjId": {
"$convert": {
"input": "$chats.messages.senderId",
"to": "objectId",

}
}
}
},
{
"$lookup": {
"from": "users",
"localField": "chats.messages.senderIdObjId",
"foreignField": "_id",
"as": "chats.messages.sender"
}
},
{
"$unwind": "$chats.messages.sender"
},
{
"$group": {
"_id": "$chats.id",
"messages": {
"$push": "$chats.messages"
},
"allFields": {
"$first": "$$ROOT"
}
}
},
{
"$addFields": {
"allFields.chats.messages": "$messages"
}
},
{
"$replaceWith": "$allFields"
},
{
"$group": {
"_id": "$_id",
"chats": {
"$push": "$chats"
},
"allFields": {
"$first": "$$ROOT"
}
}
},
{
"$addFields": {
"allFields.chats": "$chats"
}
},
{
"$replaceWith": "$allFields"
},

])

这里的工作游乐场