时间戳

首页 > 解决方案 > 使用 $addFields 时如何将值合并到一个数组中？

mongodb - 使用 $addFields 时如何将值合并到一个数组中？

问题描述

我是 MongoDB 的新手，基本上来自 RDBMS。我正在使用 $addFields 从连接表中检索单个列，如下所示，

Movies集合结构：

{
    "_id" : ObjectId("5cd30088ccbfc800173dcd42"),
    "name" : "NGK",
    "sourceId" : "5cd3007cccbfc800173dcd41"
}

Events集合结构：

{
    "_id" : ObjectId("5cfa8b20ee38ea10717b3e72"),
    "movieId" : ObjectId("5cd720dda185d600170278fd"),
    "type" : "news",
    "description" : "Kathua rape, murder case verdict LIVE updates: Victim's family likely to challenge verdict giving life term to three main accused",
    "resources" :{
        "posterLinks" : [ 
            {
                "_v" : 1560167750,
                "image" : "xxxx/posters/orgomohvopr0ykxgmq2g"
            }, 
            {
                "_v" : 1560167752,
                "image" : "xxxx/posters/bnskv36qbdj9wnaomvre"
            }
        ],
        "youtubeLinks" : [ 
            "https://www.youtube.com/watch?v=ocnzgwjXdLw", 
            "https://www.youtube.com/watch?v=oLRHNYQ-Dno"
        ]
    },
    "createdDate" : ISODate("2019-06-07T16:04:48.980Z")
}, 
{
    "_id" : ObjectId("5cfa8d03e8604410c74a65ab"),
    "movieId" : ObjectId("5cd720dda185d600170278fd"),
    "sourceId" : ObjectId("5cfa0bb8f5b21607b245eff3"),
    "type" : "news",
    "description" : "Former Pakistani president Asif Ali Zardari arrested on corruption charges",
    "resources" : {
        "posterLinks" : [ 
            {
                "_v" : 1560167915,
                "image" : "xxxx/posters/ldu3iceqarnaaw1qeyhk"
            }, 
            {
                "_v" : 1560167917,
                "image" : "xxxx/posters/r1oi172lpxdylnqaify5"
            }
        ]
    },
    "createdDate" : ISODate("2019-06-07T16:12:51.927Z")
}

我的查询：

db.getCollection('movies').aggregate([
   {
        $lookup: {
            from: "events",
            localField: "_id",
            foreignField: "movieId",
            as: "events"
        }
   },
   { 
       $addFields: { 
           "resources": "$events.resources"
        }
   }
]);

结果如下，

{
    "_id" : ObjectId("5cd720dda185d600170278fd"),
    "action" : [ 
        "5cf35c027807f0f65b5e9627"
    ],
    "events" : [ 
        {
            "_id" : ObjectId("5cfa8b20ee38ea10717b3e72"),
            "movieId" : ObjectId("5cd720dda185d600170278fd"),
            "sourceId" : ObjectId("5cfa0bb8f5b21607b245eff3"),
            "type" : "news",
            "description" : "Kathua rape, murder case verdict LIVE updates: Victim's family likely to challenge verdict giving life term to three main accused",
            "resources" :{
                "posterLinks" : [ 
                    {
                        "_v" : 1560167750,
                        "image" : "xxxx/posters/orgomohvopr0ykxgmq2g"
                    }, 
                    {
                        "_v" : 1560167752,
                        "image" : "xxxx/posters/bnskv36qbdj9wnaomvre"
                    }
                ],
                "youtubeLinks" : [ 
                    "https://www.youtube.com/watch?v=ocnzgwjXdLw", 
                    "https://www.youtube.com/watch?v=oLRHNYQ-Dno"
                ]
            },
            "createdDate" : ISODate("2019-06-07T16:04:48.980Z")
        }, 
        {
            "_id" : ObjectId("5cfa8d03e8604410c74a65ab"),
            "movieId" : ObjectId("5cd720dda185d600170278fd"),
            "sourceId" : ObjectId("5cfa0bb8f5b21607b245eff3"),
            "type" : "news",
            "description" : "Former Pakistani president Asif Ali Zardari arrested on corruption charges",
            "resources" : {
                "posterLinks" : [ 
                    {
                        "_v" : 1560167915,
                        "image" : "xxxx/posters/ldu3iceqarnaaw1qeyhk"
                    }, 
                    {
                        "_v" : 1560167917,
                        "image" : "xxxx/posters/r1oi172lpxdylnqaify5"
                    }
                ]
            },
            "createdDate" : ISODate("2019-06-07T16:12:51.927Z")
        }, 
    "resources" : [ 
        {
            "posterLinks" : [ 
                {
                    "_v" : 1560167750,
                    "image" : "xxxx/posters/orgomohvopr0ykxgmq2g"
                }, 
                {
                    "_v" : 1560167752,
                    "image" : "xxxx/posters/bnskv36qbdj9wnaomvre"
                }
            ],
            "youtubeLinks" : [ 
                "https://www.youtube.com/watch?v=ocnzgwjXdLw", 
                "https://www.youtube.com/watch?v=oLRHNYQ-Dno"
            ]
        }, 
        {
            "posterLinks" : [ 
                {
                    "_v" : 1560167915,
                    "image" : "xxxx/posters/ldu3iceqarnaaw1qeyhk"
                }, 
                {
                    "_v" : 1560167917,
                    "image" : "xxxx/posters/r1oi172lpxdylnqaify5"
                }
            ]
        }
    ]
}

我得到了预期的结果，但是正如您所看到的，当电影有多个事件时，当电影有多个事件时，资源中有多个数组。

我正在寻找一种方法来为每个具有单个posterLinks和youtubeLinks对象的电影获取单个资源数组，我知道这可以通过在检索值后使用循环来实现。但是有没有办法在查询本身中做到这一点？

此外，我不需要在结果中再次使用 events 数组，因为所需的资源已经在结果中可用。对此的任何建议将不胜感激。

标签： mongodbmongoose

解决方案

对于任意资源，您可以使用查找的子查询语法

像这样的东西：

db.getCollection('movies').aggregate([
    {
       $lookup:
         {
            from: "events",
            let: { movieId: "$_id" },
            pipeline: [ 
                { $match: { $expr: { $eq: ["$movieId", "$$movieId"] } } },
                { $project: { resources: { $objectToArray: "$resources" } } },
                { $unwind: "$resources" },
                { $unwind: "$resources.v" },
                { $group: { _id:"$resources.k", "v": { $addToSet:"$resources.v" } } },
                { $group: { _id:null, resources: { $push: { k:"$_id", v:"$v" } } } },
                { $project: { resources: { $arrayToObject: "$resources" } } },
                { $replaceRoot: { newRoot:"$resources" } }
            ],
            as: "events"
         }
    },
    { $project: { resources:0 } },
    { $unwind: "$events" }
]);

查找中的管道按键对资源进行分组。它允许结果数组中的重复资源。如果不打算这样做，则应相应地修改子查询管道。

请记住，这是非常昂贵的查询。如果资源结构是预定义且稳定的，则可以通过在主管道中使用带有硬编码键的数组函数来提高查询效率。

推荐阅读