mongodb - 具有嵌套对象数组的 MongoDB 聚合
问题描述
我正在努力从 Mongo DB 中获取一些汇总数据。我有以下收藏:
餐厅:
{
"_id" : ObjectId("5e0ff6d424f9fc12bc3d9464"),
"name" : "Pizzaria Don Juan",
"active" : true,
"users" : [
{
"_id" : ObjectId("5e10fc2adc147a373c312144")
},
{
"_id" : ObjectId("5e11ff8003eb832ef84342a6")
}
],
"socialMedias" : [
{
"_id" : ObjectId("5e1008943330ad05d4e1867c"),
"url" : "https://instagram/jetpizzas"
},
{
"_id" : ObjectId("5e10089a3330ad05d4e1867d"),
"url" : "https://facebook.com/jetpizzas"
}
],
"branches" : [
{
"name" : "Teste"
},
{
"name" : "Teste 2"
}
],
"sections" : [
{
"name" : "Bebidas"
}
],
"__v" : 0
}
{
"_id" : ObjectId("5e0ffd23991918424c8d7c3b"),
"name" : "Pizza Ruth",
"active" : true,
"users" : [ ],
"socialMedias" : [ ],
"branches" : [ ],
"sections" : [ ],
"__v" : 0
}
{
"_id" : ObjectId("5e0ffd3d991918424c8d7c3c"),
"name" : "Feijão de Corda",
"active" : true,
"users" : [ ],
"socialMedias" : [ ],
"branches" : [ ],
"sections" : [ ],
"__v" : 0
}
用户
{
"_id" : ObjectId("5e10fc2adc147a373c312144"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : 2,
"active" : true,
"username" : "contato@pizzariadonjuan.com.br",
"password" : "$2a$10$xhmw83QXbMvSqmrKAUYn.O4fOxboEyVkVB0DGkSsJUOp7K4bYQkCm",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-04T20:57:14.634Z"),
"__v" : 0
}
{
"_id" : ObjectId("5e11ff8003eb832ef84342a6"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : 2,
"active" : true,
"username" : "sac@pizzariadonjuan.com.br",
"password" : "$2a$10$wby3cs89jyO0HUbEiGLKye0jOB3U295zzIsu8xGJ4wnQtw5jcvSZO",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-05T15:23:44.386Z"),
"__v" : 0
}
{
"_id" : ObjectId("5e11ff9c03eb832ef84342a7"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : 2,
"active" : true,
"username" : "juan@pizzariadonjuan.com.br",
"password" : "$2a$10$nEM3RxEjYbI77R9vOWUrMOGeHFDmdZqVKUNtTLuKZVLNQBQqIbew.",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-05T15:24:12.456Z"),
"__v" : 0
}
简介
{
"_id" : ObjectId("5e0ea5f6832df0473cacacda"),
"number" : 1,
"name" : "Cliente",
"__v" : 0
}
{
"_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
"number" : 2,
"name" : "Restaurante",
"__v" : 0
}
{
"_id" : ObjectId("5e0ea607832df0473cacacdc"),
"number" : 0,
"name" : "Admin",
"__v" : 0
}
和社交媒体:
{
"_id" : ObjectId("5e1008943330ad05d4e1867c"),
"name" : "Instagram",
"__v" : 0
}
{
"_id" : ObjectId("5e10089a3330ad05d4e1867d"),
"name" : "Facebook",
"__v" : 0
}
{
"_id" : ObjectId("5e1009043330ad05d4e1867f"),
"name" : "LinkedIn",
"__v" : 0
}
我的目标是获取与餐厅对象相关的所有对象。使用以下代码:
db.restaurants.aggregate([
{ $lookup: { from: "users", localField: "users._id", foreignField: "_id", as: "foundUsers" } },
{$group: {
'_id': '$_id',
'name': { "$first": "$name" },
'active': { "$first": "$active" },
users: { $push: '$foundUsers' },
branches: { "$first": "$branches" },
sections: { "$first": "$sections" },
socialMedias: { "$first": "$socialMedias" }
}
},
{$unwind: '$users'},
{ $unset: 'users.password' },
{ $lookup: { from: "profiles", localField: "users.profile", foreignField: "number", as: "profile" } },
{ $addFields: { 'users.profile': { $arrayElemAt: ['$profile', 0] } } },
{ $unset: 'profile' },
{ $lookup: { from: "socialmedias", localField: "socialMedias._id", foreignField: "_id", as: "socialMedia" } },
{ $addFields: { 'socialMedias.name': { $arrayElemAt: ['$socialMedia.name', 0] } } },
{$group: {
'_id': '$_id',
'name': { "$first": "$name" },
'active': { "$first": "$active" },
users: { $first: '$users' },
branches: { "$first": "$branches" },
sections: { "$first": "$sections" },
socialMedias: { "$first": "$socialMedias" }
}
}
])
我得到这个:
{
"_id" : ObjectId("5e0ffd3d991918424c8d7c3c"),
"name" : "Feijão de Corda",
"active" : true,
"users" : [ ],
"branches" : [ ],
"sections" : [ ],
"socialMedias" : [ ]
}
{
"_id" : ObjectId("5e0ffd23991918424c8d7c3b"),
"name" : "Pizza Ruth",
"active" : true,
"users" : [ ],
"branches" : [ ],
"sections" : [ ],
"socialMedias" : [ ]
}
{
"_id" : ObjectId("5e0ff6d424f9fc12bc3d9464"),
"name" : "Pizzaria Don Juan",
"active" : true,
"users" : [
{
"_id" : ObjectId("5e10fc2adc147a373c312144"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : {
"_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
"number" : 2,
"name" : "Restaurante",
"__v" : 0
},
"active" : true,
"username" : "contato@pizzariadonjuan.com.br",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-04T20:57:14.634Z"),
"__v" : 0
},
{
"_id" : ObjectId("5e11ff8003eb832ef84342a6"),
"isExpired" : false,
"isBlocked" : false,
"loginTentatives" : 0,
"profile" : {
"_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
"number" : 2,
"name" : "Restaurante",
"__v" : 0
},
"active" : true,
"username" : "sac@pizzariadonjuan.com.br",
"email" : "",
"phone" : "",
"createdAt" : ISODate("2020-01-05T15:23:44.386Z"),
"__v" : 0
}
],
"branches" : [
{
"name" : "Teste"
},
{
"name" : "Teste 2"
}
],
"sections" : [
{
"name" : "Bebidas"
}
],
"socialMedias" : [
{
"_id" : ObjectId("5e1008943330ad05d4e1867c"),
"url" : "https://instagram/jetpizzas",
"name" : "Instagram"
},
{
"_id" : ObjectId("5e10089a3330ad05d4e1867d"),
"url" : "https://facebook.com/jetpizzas",
"name" : "Instagram"
}
]
}
请注意,嵌套数组socialMedias
的社交媒体名称值错误(重复的“Instagram”名称,它应该是 Instagram 的一条记录和 Facebook 的另一条记录)。即使我尝试从餐厅集合中展开 socialMedias 数组,它也只返回具有社交媒体值的餐厅对象。
关于如何做到这一点的任何线索?
解决方案
将结果与现有数组合并的方式$lookup
是这里的一个问题。你不能运行:
{ $addFields: { 'socialMedias.name': { $arrayElemAt: ['$socialMedia.name', 0] } } },
因为你总是会得到第一个数组元素。您需要使用$map、$filter和$mergeObjects合并两个数组:
{
$addFields: {
socialmedias: {
$map: {
input: "$socialMedias",
as: "sm",
in: {
$mergeObjects: [
"$$this",
{
$arrayElemAt: [ { $filter: { input: "$socialmedias", cond: { $eq: [ "$$sm.number", "$$this._id" ] } } }, 0 ]
}
]
}
}
}
}
}
您还需要应用它,user.profile
因为当前的解决方案容易出错。
推荐阅读
- reactjs - useState 加载状态未触发
- svg - 为什么在 PimCore v6.3.6 中不呈现 svg 预览?
- javascript - Highcharts Angular Chart 不是来自 renderer.button 事件的 redrwan
- java - JPQL/HQL 使用 spring 数据 jpa 删除查询
- django - 禁用字段导致 Django 中的表单验证错误
- javascript - 带 2 个小数点的年龄正则表达式
- java - 使用java在Apache spark中获取单列值作为平面列表
- python - 返回给定字典键的下一个键,python 3.6+
- reactjs - 为什么我得到方法“props”只意味着在单个节点上运行。在单元测试中发现 0?
- java - 如何从具有 2 个字符输出的日期字符串中获取周数