How to get document which is referenced to object?

Question

I have three collections Subscribers,Address,Languages

Subscribers schema has following record

{
        "_id" : ObjectId("5a73fae80290f7eca89e99d4"),
        "FirstName" : "Rahul",
        "LastName" : "Shah",            
        "Address" : DBRef("Address", ObjectId("5a6ec9bda3baf2b516de5769")),            
        "Languages" : [
                DBRef("Languages", ObjectId("5a6304ffc3c3f119fc0e60c9")),
                DBRef("Languages", ObjectId("5a6ee970a3baf2b516de576b"))
        ]
}

Address schema has following record

{
        "_id" : ObjectId("5a6ec9bda3baf2b516de5769"),
        "Address1" : "Vastrapur,
        "Address2" : "Satellite",
        "City" : "Ahmedabad",
        "Country" : "India",
        "State" : "Gujarat",
        "ZipCode" : "380015",
        "PhoneNumber" : "(987)654-3210",
        "FaxNumber" : ""
}

Languages schema has following record

{
        "_id" : ObjectId("5a6304ffc3c3f119fc0e60c9"),
        "Name" : "English",
        "LanguageCulture" : "English",
        "IsDeleted" : false
}
{
        "_id" : ObjectId("5a6ee970a3baf2b516de576b"),
        "Name" : "Hindi",
        "LanguageCulture" : "Hindi",
        "IsDeleted" : false
}

I want output be like as below

{
    "_id" : ObjectId("5a73fae80290f7eca89e99d4"),
    "FirstName" : "Rahul",
    "LastName" : "Shah",
    "Address" : {
        "_id" : ObjectId("5a6ec9bda3baf2b516de5769"),
        "Address1" : "Vastrapur",
        "Address2" : " Satellite ",
        "City" : " Ahmedabad ",
        "Country" : " India ",
        "State" : " Gujarat ",
        "ZipCode" : " 380015 ",
        "PhoneNumber" : "(987)654 - 3210 ",
        "FaxNumber" : " "
    },
    "Languages" : [{
            "_id" : ObjectId(" 5a6304ffc3c3f119fc0e60c9 "),
            " Name" : " English ",
            "LanguageCulture" : " English ",
            "IsDeleted" : false
        }, {
            "_id" : ObjectId(" 5a6ee970a3baf2b516de576b "),
            "Name" : " Hindi ",
            "LanguageCulture" : " Hindi ",
            "IsDeleted" : false
        }
    ]
}

For that Im doing aggregation as below

db.Subscribers.aggregate([{
            $project : {
                "FirstName" : 1,
                "LastName" : 1,
                Address : {
                    $let : {
                        vars : {
                            refParts : {
                                $objectToArray : "$$ROOT.Address"
                            }
                        },
                        in : "$$refParts"
                    }
                }
            }
        }, {
            $match : {
                "addressRefs" : {
                    $exists : true
                }
            }
        }, {
            $project : {
                "FirstName" : 1,
                "LastName" : 1,             
                "addressRefs" : {
                    $arrayElemAt : ["$addressRefs", 1]
                }
            }
        }, {
            $lookup : {
                from : "Addresses",
                localField : "addressRefs",
                foreignField : "_id",
                as : "address_data"
            }
        }, {
            $project : {
                "FirstName" : 1,
                "LastName" : 1,             
                "AddressUuid" : {
                    $arrayElemAt : ["$address_data.uuid", 0]
                }
            }
        }
    ])

Can you show us your code so far? – vektor Feb 19 '18 at 13:06 — vektor, Feb 19 '18 at 13:06

dnickless · Accepted Answer · 2018-04-15T12:53:33.930

Here you go. It's not pretty because of your use of DBRefs but it gets the job done thanks to the following SO answer:

Mongo how to $lookup with DBRef

db.Subscribers.aggregate([{
    $addFields: { 
        "Address": {$arrayElemAt: [{$objectToArray: "$Address"}, 1]}, // simple DBRef transformation if the field is not an array of DBRefs
        "Languages": {
            $map: { 
                input: { 
                    $map: {
                        input: "$Languages",
                        in: {
                            $arrayElemAt: [{$objectToArray: "$$this"}, 1]
                        },
                    }
                },
                in: "$$this.v"
            }
        }
    }
}, {
    $lookup: {
       from: "Address",
       localField: "Address.v", // note the .v here
       foreignField: "_id",
       as: "Address"
    }
}, {
    $lookup: {
       from: "Languages",
       localField: "Languages",
       foreignField: "_id",
       as: "Languages"
    }
}, {
    $addFields: {
        "Address": { $arrayElemAt: [ "$Address", 0 ] } // you can replace this step with an $unwind to 'flatten' the single-value array but you might want to use preserveNullAndEmptyArrays: true in order to cater for subscribers that come without valid Address references
    }
}])

In the case of the Adress field, since it's not an array, you need to change the query to $addFields: { "Address": {$arrayElemAt: [{$objectToArray: "$Address"}, 1]}}, I have edited your answer, hope you don't mind. — Olivier Maurel, Apr 10 '18 at 19:37

How to get document which is referenced to object?

1 Answers1