mongodbdbref

Mongo Lookup with nested DBRefs


I have a collection in MongoDB called 'post' with a list of DBRefs to 'author' which in turn has DBRef to a collection 'media'.

When I retrieve posts, I would like to get the entire object for post including all authors and media within the object.

For eg here are my collections:

post:
{
    "_id" : ObjectId("5d7db7af49c5e277395871bd"),
    "authors" : [
        DBRef("author", ObjectId("5d7daaab49c5e277395871ba")),
        DBRef("author", ObjectId("5d7daaab49c5e277395871bb"))
    ]
}

author:
[{
    "_id" : ObjectId("5d7daaab49c5e277395871ba"),
    "name" : "author 1",
    "media" : DBRef("media", ObjectId("5d7daa2049c5e277395871b8"))
},
{
    "_id" : ObjectId("5d7daaab49c5e277395871bb"),
    "name" : "author 2",
    "media" : DBRef("media", ObjectId("5d7daa2049c5e277395871b9"))
}]

media:
[{
    "_id" : ObjectId("5d7daa2049c5e277395871b8"),
    "url" : "http://image.com/1",
    "type" : "png"
},
{
    "_id" : ObjectId("5d7daa2049c5e277395871b9"),
    "url" : "http://image.com/2",
    "type" : "png"
}]

I would want to have the following result for the query:

post:
{
    "_id" : ObjectId("5d7db7af49c5e277395871bd"),
    "authors" : [
            {
                "_id" : ObjectId("5d7daaab49c5e277395871ba"),
                "name" : "author 1",
                "media" : {
                    "_id" : ObjectId("5d7daaab49c5e277395871ba"),
                    "name" : "author 1",
                    "media" : DBRef("media", ObjectId("5d7daa2049c5e277395871b8"))
                }
            },
            {
                "_id" : ObjectId("5d7daaab49c5e277395871bb"),
                "name" : "author 2",
                "media" : {
                    "_id" : ObjectId("5d7daaab49c5e277395871bb"),
                    "name" : "author 2",
                    "media" : DBRef("media", ObjectId("5d7daa2049c5e277395871b9"))
                }
            }
    ]
}

So far, I have the following query that includes the authors within a post, but I need help with retrieving the media as well in the author.

current query:

db.post.aggregate([
{
    $project: { 
        authors: {
          $map: { 
             input: { 
                  $map: {
                      input:"$authors",
                      in: {
                           $arrayElemAt: [{$objectToArray: "$$this"}, 1]
                      },
                  }
             },
             in: "$$this.v"}},
        }

}, 
{
    $lookup: {
        from:"author", 
        localField:"authors",
        foreignField:"_id", 
        as:"authors"
    }
},
])

current result:

{
    "_id" : ObjectId("5d7db7af49c5e277395871bd"),
    "authors" : [
        {
            "_id" : ObjectId("5d7daaab49c5e277395871ba"),
            "name" : "author 1",
            "media" : DBRef("media", ObjectId("5d7daa2049c5e277395871b8"))
        },
        {
            "_id" : ObjectId("5d7daaab49c5e277395871bb"),
            "name" : "author 2",
            "media" : DBRef("media", ObjectId("5d7daa2049c5e277395871b9"))
        }
    ]
}

Solution

  • try with $unwind and then $goupby id

    you can do it like this

    db.post.aggregate([{
        $project: {
            authors: {
                $map: {
                    input: {
                        $map: {
                            input: "$authors",
                            in: {
                                $arrayElemAt: [{
                                    $objectToArray: "$$this"
                                }, 1]
                            },
                        }
                    },
                    in: "$$this.v"
                }
            },
        }
    }, {
        $lookup: {
            from: "author",
            localField: "authors",
            foreignField: "_id",
            as: "authors"
        }
    }, {
        $unwind: "$authors"
    }, {
        $project: {
            "authors.media": {
                $arrayElemAt: [{
                    $objectToArray: "$authors.media"
                }, 1]
            },
            "authors._id": 1,
            "authors.name": 1
        }
    }, {
        $lookup: {
            from: "media",
            localField: "authors.media.v",
            foreignField: "_id",
            as: "authors.media"
        }
    }, {
        $unwind: {
            path: "$authors.media",
            preserveNullAndEmptyArrays: true
        }
    }, {
        $group: {
            _id: "$_id",
            authors: {
                $push: "$authors"
            }
        }
    }]).pretty()
    
    

    here is the result

    
        "_id" : ObjectId("5d7db7af49c5e277395871bd"),
        "authors" : [
            {
                "_id" : ObjectId("5d7daaab49c5e277395871ba"),
                "name" : "author 1",
                "media" : {
                    "_id" : ObjectId("5d7daa2049c5e277395871b8"),
                    "url" : "http://image.com/1",
                    "type" : "png"
                }
            },
            {
                "_id" : ObjectId("5d7daaab49c5e277395871bb"),
                "name" : "author 2",
                "media" : {
                    "_id" : ObjectId("5d7daa2049c5e277395871b9"),
                    "url" : "http://image.com/2",
                    "type" : "png"
                }
            }
        ]
    }
    

    you can change the format with $project in the middle stage

    what is more
    $unwind can use with option . The following $unwind operation uses the preserveNullAndEmptyArrays option to include in the output those documents where sizes field is missing, null or an empty array.

    [
       { $unwind: { path: "$sizes", preserveNullAndEmptyArrays: true } }
    ]
    

    here is the documents