CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
SantoshVysyaraju
SantoshVysyaraju

Reputation: 59

Mongo Query: how to $lookup with DBRef

I have a trouble with $lookup with DBRef. I couldn't find the solution for below scenario anywhere. Someone please help me here?

Suppose the Collection A is

{ 
"_id" : ObjectId("582abcd85d2dfa67f44127e0"),  
"status" : NumberInt(1), 
"seq" : NumberInt(0)    }

and Collection B:

{ 
"_id" : ObjectId("582abcd85d2dfa67f44127e1"),
"Name" : "from B Collection"
"bid" : DBRef("B", ObjectId("582abcd85d2dfa67f44127e0"))   }

I have spent lot of time in aggregating above two collections. I am looking for the output as below.

{ 
"_id" : ObjectId("582abcd85d2dfa67f44127e0"),  
"status" : NumberInt(1), 
"seq" : NumberInt(0),
B: [
    {
        "_id" : ObjectId("582abcd85d2dfa67f44127e1"),
        "Name" : "from B Collection"
    }
]}

Please help me with the Mongo query to retrieve the result in the above format. Thanks in advance

Upvotes: 3

Views: 3317

Answers (1)

Katherine R
Katherine R

Reputation: 1158

Ideally you would be able to change the DBRef to a plain objectId or just string type. As noted in this post, it can be convoluted to use a DBRef in a lookup. The key is an $addFields stage with {$objectToArray: "$$ROOT.bid"} to get the DBRef value into a usable format.

You'll need to start the aggregation from collection B since that is where the reference is -- and that DBRef needs massaging before doing the lookup. Knowing that is the case, maybe the goal output shape might change; however, here is an aggregation that works to get you what you need:

db.getCollection('B').aggregate([
{$addFields: {fk: {$objectToArray: "$$ROOT.bid"}}},
{$lookup: {
    from: 'A',
    localField: 'fk.1.v',
    foreignField: '_id',
    as: 'A'
}},
// the below is transforming data into the format in the example
{$addFields: {'A.B': {_id: '$_id', Name: '$Name'}}},
{$unwind: '$A'},
{$replaceRoot: {newRoot: '$A'}}
])

You might need to do a groupBy if there are multiple B matches you need to group into an array.

Upvotes: 9

Related Questions