MongoDB joining across array of ids

user_12466812 0
Viewed 61

Before the question, I'm extremely new to mongo DB and NoSQL.

I'm having two collections in my database:

users:

{ 
  "_id" : ObjectId("5f1efeece50f2b25d4be2de2"), 
  "name" : { 
     "familyName" : "Doe", 
     "givenName" : "John" 
   }, 
  "email" : "johndoe@example.com", 
  "threads" : [ObjectId("5f1f00f31abb0e3f107fbf93"), ObjectId("5f1f0725850eca800c70ef9e") ] }
}

threads:

{ 
  "_id" : ObjectId("5f1f0725850eca800c70ef9e"), 
  "thread_participants" : [ ObjectId("5f1efeece50f2b25d4be2de2"), ObjectId("5f1eff1ae50f2b25d4be2de4") ], 
  "date_created" : ISODate("2020-07-27T16:25:19.702Z") }
}

I want to get all the threads which an user is involved in with the other user's info nested inside. Something like:

{ 
  "_id" : ObjectId("5f1f0725850eca800c70ef9e"), 
  "thread_participants" : 
    [ 
      {
        "name" : { 
          "familyName" : "Doe", 
          "givenName" : "John" 
        }, 
      "email" : "johndoe@example.com",                    
      }, 
      {
        "name" : { 
          "familyName" : "Doe", 
          "givenName" : "Monica" 
        }, 
      "email" : "monicadoe@example.com",                    
      }
    ], 
  "date_created" : ISODate("2020-07-27T16:25:19.702Z") }
},
...,
...,
...

How do I go about this?

aggregation-framework mongodb nosql database
1 Answers

You can use $lookup to "join" the data from both collections:

db.threads.aggregate([
    {
        $lookup: {
            from: "$users",
            let: { participants: "$thread_participants" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $in: [ "$_id", "$$participants" ]
                        }
                    }
                },
                {
                    $project: {
                        _id: 1,
                        email: 1,
                        name: 1
                    }
                }
            ],
            as: "thread_participants"
        }
    }
])

Mongo Playground

user_62389770
Related