MongoDB nested aggregation (join)

Question

I have one document that I want to run nested aggregation on this, but I don't know how to run this operation.

Content document (removed unnecessary fields):

{
    "_id" : ObjectId("5a6b8b734f1408137f79e2cc"),
    "reviews" : [ 
        {
            "_id" : ObjectId("5a6cf7c41562160494238781"),
            "headline" : "",
            "body" : "",
            "likeUsers" : [ 
                {
                    "_id" : ObjectId("5a6b5f2e1fc2a11c5c83a6e2")
                }
            ],
            "dislikeUsers" : [],
            "isCritic" : false,
            "isSpoilers" : false,
            "isTop" : true,
            "rate" : 3,
            "userId" : ObjectId("5a6b5f2e1fc2a11c5c83a6e2"),
            "date" : Timestamp(1517090823, 1)
        }
    ]
}

user content:

{
    "_id" : ObjectId("5a6b5f2e1fc2a11c5c83a6e2"),
    "username" : "",
    "password" : "",
    "firstname" : "",
    "lastname" : "",
    "email" : "",
    "mobile" : "",
    "tel" : "",
    "nationalCode" : "",
    "gender" : "male",
    "birthdate" : Timestamp(0, 1),
    "promotionCode" : "12131SKSQ",
    "status" : 1,
    "created" : Timestamp(1516986633, 1),
    "updated" : Timestamp(1516986633, 2)
}

I want to get all users information from likeUsers array field.

@EhsanFarahaniAsil you can use $lookup aggregate pipeline for joining with other collection — Saravana
– Saravana, Commented Jan 28, 2018 at 7:36

Saravana · Accepted Answer · 2018-01-28 09:11:31Z

1

you can use $lookup aggregation to get the liked user's info as an embedded array

db.content.aggregate(
    [
        {$match : {"_id" : ObjectId("5a6b8b734f1408137f79e2cc")}},
        {
            $lookup : {
                from : "user",
                localField : "reviews.likeUsers._id",
                foreignField : "_id",
                as : "likeUsersInfo"
            }
        }
    ]
)

EDIT-1

to have embedded documents in same hierarchy

db.content.aggregate(
    [
        {$match : {"_id" : ObjectId("5a6b8b734f1408137f79e2cc")}},
        {
            $lookup : {
                from : "user",
                localField : "reviews.likeUsers._id",
                foreignField : "_id",
                as : "likeUsersInfo"
            }
        },
        {$addFields : {"reviews.likeUsers" : "$likeUsersInfo"}},
        {$project : {likeUsersInfo : 0}}
    ]
).pretty()

edited Jan 28, 2018 at 9:11

answered Jan 28, 2018 at 7:13

Saravana

12.8k2 gold badges42 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Saravana Over a year ago

yes, the aggregation returns cursor with this format

Ehsan Farahani Asil Over a year ago

how to limit users information field? i mean return only these field (firstName,lastName,username)?

Saravana Over a year ago

see $project documentation to exclude embedded fields

Ehsan Farahani Asil Over a year ago

thanks. i used this query ({$project : {likeUsersInfo :0,reviews.likeUsers.password:0}}) but after run it, return error (Error: Line 13: Unexpected token .)

Saravana Over a year ago

enclose fields with double quotes

|

Ehsan Farahani Asil · Accepted Answer · 2018-01-28 08:04:50Z

0

Thanks very much for your response. is possible that get user information exactly in reviews.likerUsers? i need return result like this:

{
    "_id" : ObjectId("5a6b8b734f1408137f79e2cc"),
    "reviews" : [ 
        {
            "_id" : ObjectId("5a6cf7c41562160494238781"),
            "headline" : "",
            "body" : "",
            "likeUsers" : [ 
                {
                  "_id" : ObjectId("5a6b5f2e1fc2a11c5c83a6e2"),
                  "username" : "",
                  "firstname" : "",
                  "lastname" : ""
                }
            ],
            "dislikeUsers" : [],
            "isCritic" : false,
            "isSpoilers" : false,
            "isTop" : true,
            "rate" : 3,
            "userId" : ObjectId("5a6b5f2e1fc2a11c5c83a6e2"),
            "date" : Timestamp(1517090823, 1)
        }
    ]
}

answered Jan 28, 2018 at 8:04

Ehsan Farahani Asil

631 gold badge2 silver badges10 bronze badges

1 Comment

Saravana Over a year ago

you should post this in a new question? you should not add/update answered questions?

Ehsan Farahani Asil · Accepted Answer · 2018-01-28 16:33:15Z

finally i can get user information in exactly tags with $addFields and $project command. i added another $project for limited user information fields. operation run success and reviews.likeUsers.[users] fields is limited but contents fields doesn't show.

New query:

db.contents.aggregate(
    // Pipeline
    [
        // Stage 1
        {
            $lookup: // Equality Match
            {
                            from : "users",
                            localField : "reviews.likeUsers._id",
                            foreignField : "_id",
                            as : "likeUsersInfo"
            }
        },

        // Stage 2
        {
            $addFields: {"reviews.likeUsers" : "$likeUsersInfo"}
        },

        // Stage 3
        {
            $project: {likeUsersInfo : 0}
        },

        // Stage 4
        {
            $project: {"reviews.likeUsers.username": 1,"reviews.likeUsers.firstname": 1,"reviews.likeUsers.lastname": 1}
        },

    ]
);

after run above query return below fields and other contents field doesn't show:

{
    "_id" : ObjectId("5a6b8b734f1408137f79e2cc"),
    "reviews" : [ 
        {
            "likeUsers" : [ 
                {
                    "username" : "e.farahani",
                    "firstname" : "Ehsan",
                    "lastname" : "Farahani Asil"
                }
            ]
        }
    ]
}

Collectives™ on Stack Overflow

MongoDB nested aggregation (join)

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related