How to join MongoDB with DBRef

Question

I have two documents with a DBRef relation between those documents.

Task document:

{
  "_id": 77,
  "title": "Test title",
  "status": "in-progress",
  "reporter": {
    "$ref": "User",
    "$id": ObjectId("5daf022549a36e319879f357"),
    "$db": "test"
  },
  "priority": "high",
  "project": {
    "$ref": "Project",
    "$id": 30,
    "$db": "gsc"
  }
}

User Document:

{
  "_id": ObjectId("5daf022549a36e319879f357"),
  "username": "user1",
  "email": "[email protected]",
  "is_active": true,
  "firstName": "user-1"
}

I tried below query but I didn't get proper result

db.Task.findOne($lookup:
  {
    from: User,
    localField: reporter,
    foreignField: reporter._id,
    as: User_Task
  }
)

How to perform the JOIN? Also, want to both document all data? I want data from Task Document and suggest how to join with project field.

Need this kind of result:

{
    "_id" : 77,
    "title" : "Test title",
    "status" : "in-progress"
    "reporter" : 
    {
        "_id" : ObjectId("5daf022549a36e319879f357"),
        "username" : "user1",
        "email" : "[email protected]"
        "is_active" : true,
        "firstName" : "user-1"
    },
    "priority" : "high",
    "project" : {}
}

Does this answer your question? How do I perform the SQL Join equivalent in MongoDB? — B--rian
No, This does not work for me this was for match records and i want a relational records. hope you understand. — Ravi Damasiya
Sure, I just saved the information from @lucferbux before it disappears - it was flagged for deletion. — B--rian
I saw that, I also did fixed some typesetting for you. What do you mean exactly by the text in italics? — B--rian
It means I want to fire query on task document (db.Task.find({})) and join project and reporter document in the same DTO response, Do you Understand? — Ravi Damasiya

ray ray · Accepted Answer · 2021-11-01T03:09:41

A simple $lookup should do the trick. You just need to supply reporter.$id to the field localField

db.task.aggregate([
  {
    "$lookup": {
      "from": "user",
      "localField": "reporter.$id",
      "foreignField": "_id",
      "as": "reporter"
    }
  },
  {
    "$unwind": "$reporter"
  }
])

Here is the Mongo playground for your reference.

How to join MongoDB with DBRef

1 Answers