时间戳

首页 > 解决方案 > How to aggregate lookup sub array documents in mongoDB, and projecting the result?

mongodb - How to aggregate lookup sub array documents in mongoDB, and projecting the result?

问题描述

I have 2 Collections

  1. Posts
  2. Users

Posts collection contains comments array which stores { userId, comment } object, along with other information.

users ollection contains user's information.

I want to return the complete result.

Ex:

{
    "postId":"xvzeee",
    "post": "Good Morning",
    "likedBy":[ 
        12342234,
        23456534
    ]
    "comments": [
    {   
        "comment": "very good morning",
        "userName": "Max"
    },
    {
        "comment": "v. GM",
        "userName": "Suraj"
    }
    ]
}

My Approach to achieve the above result is.

db.wall.aggregate([
    { $lookup: { from: 'profiles', localField: 'likedBy', foreignField: 'profileId', as: 'likedBy' } },
    { $lookup: { from: 'profiles', localField: 'comments.commentedBy', foreignField: 'profileId', as: 'commentedUser' } },
    {
        $project:{ 
            "likedBy.name": 1,
            "likedBy.profileId": 1,
            createdBy: 1,
            createdAt: 1,
            updatedAt: 1,
            comments : [{
                comment: "$comments.comment",
                user: "$commentedUser.name"
            }],
            "commentedUser.name" : 1
        }
    }
])

The result is coming like below:

{
    "_id" : ObjectId("5dfdbb129f644213c413eb18"),
    "likedBy" : [
        {
            "profileId" : "96444206",
            "name" : "Vinay3"
        },
        {
            "profileId" : "400586806",
            "name" : "Dev"
        }
    ],
    "createdBy" : "96444206",
    "commentedUser" : [
        {
            "name" : "Vinay3"
        },
        {
            "name" : "Dev"
        }
    ],
         /*Facing problem in comment array*/
    "comments" : [
        {
            "comment" : [
                "Super-awesome",
                "FAB",
            ],
            "user" : [
                "Vinay3",
                "Dev"
            ]
        }
    ]
}

The comment should look like :

[{
 ...
"comments" : [
        {
            "comments" : [
                { 
                   comment :"Super-awesome",
                   user: "Vinay3",
                   profileId: "11111..."
                },
                {
                   comment: "FAB",
                   user: "Dev",
                   profileId: "2222..."
                }
            ],
        }
    ]
}]

The Posts Collection looks like this :

[{
    "_id" : ObjectId("5dfdbb129f699913c413eb18"),
    "post":"Good Morning",
    "createdBy" : "96444206",
    "postId" : "D9644s5h8m",
    "likedBy" : [
        "96444206",
        "40058680"
    ],
    "comments" : [
        {
            "commentId" : "COM9644",
            "commentedBy" : "96444206",
            "comment" : "Super-awesome"
        },
        {
            "commentId" : "COM9644",
            "commentedBy" : "96444206",
            "comment" : "#FAB"
        },
        {
            "commentId" : "COM00587",
            "commentedBy" : "400586806",
            "comment" : "marvelous"
        }
    ],
    "createdAt" : ISODate("2019-12-21T11:56:26.944+05:30"),
    "updatedAt" : ISODate("2019-12-21T12:12:35.047+05:30"),
    "__v" : 0
}, {...}, {...}]

User Profiles Collection

[{
    "_id" : ObjectId("5dd4ff3abe53181160efa446"),
    "accountStatus" : "CONFIRMED",
    "profileId" : "400586806",
    "name" : "Dev",
    "email" : "dev@xyz.com",
    "createdAt" : ISODate("2019-11-20T14:24:18.692+05:30"),
    "updatedAt" : ISODate("2019-12-20T16:58:06.041+05:30"),
    "__v" : 0
}, {...}, {...} ]

How to achieve this, any help will much be appreciated.

标签： mongodbmongoose

解决方案

但是，我能够为我的问题提供解决方案，如下所示。

如果有比这更好的解决方案将不胜感激。

db.wall.aggregate([
    { $unwind: '$comments'},
    { $lookup: { from: 'profiles', localField: 'comments.commentedBy', foreignField: 'profileId', as: 'comments.user' } },
    { $unwind: '$comments.user' },
    { 
        $group: {
            _id:'$_id',
            postId:  { $first: "$postId"},
            likedBy: { $first: "$likedBy"},
            createdBy: { $first: '$createdBy'},
            comments: { $push : '$comments' },
            createdAt: { $first: "$createdAt" },
            updatedAt: { $first: "$updatedAt" },
        }
    },{
        $project: { 
            _id:1,
            postId: 1,
            "likedBy.name": 1,
            "likedBy.profileId": 1,
            createdBy: 1,
            comments: { 
                $map : {
                    input: '$comments',
                    as: 'com',
                    in: {
                        commentId: "$$com.commentId",
                        comment: "$$com.comment",
                        name: "$$com.user.name"
                    }
                }
            },
            createdAt: 1,
            updatedAt: 1,
        }
    }
])

我想将isActive => true || false字段添加到评论对象。现在的挑战是如何只过滤isActive => true评论？

推荐阅读