具有嵌套对象数组的MongoDB聚合

rick病

我正在努力从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" : "[email protected]",
        "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" : "[email protected]",
        "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" : "[email protected]",
        "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" : "[email protected]",
                        "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" : "[email protected]",
                        "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"
                }
        ]
}

请注意，嵌套数组

社会长袜

社交媒体名称的值错误（重复的“ Instagram”名称，对于Instagram应该是一条记录，对于Facebook应该是另一条记录）。即使我尝试从restaurant集合中解散socialMedias数组，它也仅返回作为结果具有社交媒体值的restaurant对象。

有什么线索可以解决这个问题吗？

米克尔

将$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由于当前的解决方案容易出错，因此您还需要应用此方法。

蒙哥运动场

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2021-01-22

我来说两句

0 条评论

登录后参与评论

上一篇：HTML如何仅使用html来使按钮不可点击？

具有嵌套对象数组的MongoDB聚合

具有嵌套对象数组的MongoDB聚合

蓝屏死机没有修复解决方案

计算数据帧中每行的NA

UITableView的项目向下滚动后更改颜色，然后快速备份

Node.js中未捕获的异常错误，发生调用

在 Python 2.7 中。如何从文件中读取特定文本并分配给变量

Linux的官方Adobe Flash存储库是否已过时？

验证REST API参数

ggplot：对齐多个分面图-所有大小不同的分面

Mac OS X更新后的GRUB 2问题

通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException

带有错误“ where”条件的查询如何返回结果？

用日期数据透视表和日期顺序查询

VB.net将2条特定行导出到DataGridView

如何从视图一次更新多行（ASP.NET - Core）

Java Eclipse中的错误13，如何解决？

尝试反复更改屏幕上按钮的位置 - kotlin android studio

离子动态工具栏背景色

应用发明者仅从列表中选择一个随机项一次

当我尝试下载 StanfordNLP en 模型时，出现错误

python中的boto3文件上传

在同一Pushwoosh应用程序上Pushwoosh多个捆绑ID