MongoDB joining collections over findOne

user_12466812 0
Viewed 595

I'm having this mongo db query which fetches a conversation thread and it's participant details. Tabs are a part of threads.

var threadObject = await db
        .collection("threads")
        .findOne({ tabs: { $in: [ObjectId("5f2ad2ed59645244cc6a837a")] } });

Thread schema:

{
  "_id": {
    "$oid": "5f2ad2bb59645244cc6a8379"
  },
  "thread_participants": [
    {
      "$oid": "5f2ad2a759645244cc6a8377"
    },
    {
      "$oid": "5f2ad2a159645244cc6a8375"
    }
  ],
  "tabs": [
    {
      "$oid": "5f2ad2ed59645244cc6a837a"
    }
  ],
  "date_created": {
    "$date": "2020-08-05T15:39:39.088Z"
  }
}

Users schema:

{
  "_id": {
    "$oid": "5f2ad2a159645244cc6a8375"
  },
  "name": {
    "familyName": "Doe",
    "givenName": "John"
  },
  "email": "johndoe@example.com",
  "display_picture": "url",
  "threads": [
    {
      "$oid": "5f2ad2bb59645244cc6a8379"
    }
  ]
},
{
  "_id": {
    "$oid": "5f2ad2a759645244cc6a8377"
  },
  "name": {
    "familyName": "Doe",
    "givenName": "Monica"
  },
  "email": "monicadoe@example.com",
  "display_picture": "url",
  "threads": [
    {
      "$oid": "5f2ad2bb59645244cc6a8379"
    }
  ]
}

Now the thing is I want to retrieve the user's first name and last name along with the result of this threadObject. How do I approach this?

node.js mongodb nosql javascript
1 Answers

You can either add another call:

var threadObject = await db
        .collection("threads")
        .findOne({ tabs: { $in: [ObjectId("5f2ad2ed59645244cc6a837a")] } });

var user = await db
        .collection("users")
        .findOne({ threads: threadObject._id });

threadObject.user_name = user.name;
...

Or you can use an aggregation pipeline utilising $lookup:

var threadObject = await db.collection("threads").aggregate([
    {
        $match: {
            tabs: {$in: [ObjectId("5f2ad2ed59645244cc6a837a")]}
        }
    },
    {
        $lookup: {
            from: "users",
            localField: "_id",
            foreignField: "threads",
            as: "user"
        }
    },
    {
        $unwind: "$user"
    },
    {
        $addFields: {
            user_name: "$user.name"
        }   
    },
    {
        $project: {
            user: 0
        }
    }
]).toArray()

Mind you aggregation will return an array and not an object.

user_107703700
Related