Mongodb aggregation retrieving document from inside of document

Question

I have problems with MongoDB's aggregation.

In my "Job" document, it has creatorParent(single value) and Children(array of mongodb object id). In the "User" document, user has children array with child details.

When user request for retrieving this document I want aggregate child details, if array contains id of child.

I wrote an aggregation with some help, It worked for creatorParent but whatever I've tried, it didn't worked for children.

db.getCollection('Jobs').aggregate([
    {
        $unwind: {
            path : "$children"
        }
    },
    {
        $lookup: {
            "from" : "Users",
            "localField" : "creatorparent",
            "foreignField" : "_id",
            "as" : "creatorparent"
        }
    },
    {
        $lookup: {
            "from" : "Users",
            "localField" : "children",
            "foreignField" : "children",
            "as" : "children"
        }
    }
])

Users document:

{
    "_id" : ObjectId("58daf84877733645eaa9b44f"),
    "email" : "meto93@gmail.com",
    "password" : "vpGl+Fjnef616cRgNbCkwaFDpSI=",
    "passwordsalt" : "99397F4A9D3A499D96694547667E74595CE994D2E83345D6953EF866303E8B65",
    "children" : [ 
        {
            "_id" : ObjectId("58daf84977733645eaa9b450"),
            "name" : "Mert",
            "age" : 5,
            "additionalinformation" : "ilk cocuk",
            "creationtime" : ISODate("2017-03-28T23:56:56.952Z"),
            "userid" : ObjectId("58daf84877733645eaa9b44f"),
            "gender" : null
        }, 
        {
            "_id" : ObjectId("58daf84977733645eaa9b451"),
            "name" : "Sencer",
            "age" : 7,
            "additionalinformation" : "ikinci cocuk",
            "creationtime" : ISODate("2017-03-28T23:56:56.952Z"),
            "userid" : ObjectId("58daf84877733645eaa9b44f"),
            "gender" : null
        }
    ]
}

Job

{
    "_id" : ObjectId("58db0a2d77733645eaa9b453"),
    "creationtime" : ISODate("2017-03-29T01:13:17.509Z"),
    "startingtime" : ISODate("2017-04-03T13:00:00.000Z"),
    "endingtime" : ISODate("2017-04-03T17:00:00.000Z"),
    "children" : [ 
        ObjectId("58daf84977733645eaa9b450"), 
        ObjectId("58daf84977733645eaa9b451")
    ],
    "creatorparent" : ObjectId("58daf84877733645eaa9b44f"),
    "applicants" : []
}

Answer 1

Try this:

db.jobs.aggregate(
    [
        {
            $unwind: {
                path : "$children",
            }
        },
        {
            $lookup: {
                "from" : "users",
                "localField" : "creatorparent",
                "foreignField" : "_id",
                "as" : "creatorparent"
            }
        },
        {
            $lookup: {
                "from" : "users",
                "localField" : "children",
                "foreignField" : "children._id",
                "as" : "children"
            }
        },
        {
            $addFields: {
                children : {$arrayElemAt : ["$children",0]}
            }
        },
        {
            $addFields: {
              "children":"$children.children"
            }
        },
        {
            $unwind: {
                path : "$children",
            }
        },
        {
            $group: {
                "_id": "$_id",
                "name": { "$first": "$name" },
                "jobstatus" : { "$first": "$jobstatus" },
                "hourlyrate" : { "$first": "$hourlyrate" },
                "creatorparent" : { "$first" : "$creatorparent" },
                "children": { "$addToSet": "$children"  }
            }
        },

    ]
);