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": "test@gmail.com",
  "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" : "test@gmail.com"
        "is_active" : true,
        "firstName" : "user-1"
    },
    "priority" : "high",
    "project" : {}
}

Does this answer your question? [How do I perform the SQL Join equivalent in MongoDB?](https://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb) — B--rian, Feb 19 '20 at 08:29
No, This does not work for me this was for match records and i want a relational records. hope you understand. — Ravi Damasiya, Feb 19 '20 at 08:40
Sure, I just saved the information from @lucferbux before it disappears - it was flagged for deletion. — B--rian, Feb 19 '20 at 08:42
I saw that, I also did fixed some typesetting for you. What do you mean exactly by the *text in italics*? — B--rian, Feb 19 '20 at 08:52
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, Feb 19 '20 at 08:57

score 1 · Answer 1 · answered Nov 01 '21 at 03:09

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 Answers1