Referencing a field from $lookup via mongoDB

Question

I am currently working with mongoDB. I have a collection called Users with the following document:

{
  _id: "5d93f338e602a10a38ad3588",
  userID: "1",
  email: "[email protected]",
  password: "password",
  firstName: "Tom",
  lastName: "Bombadil"
}

I also have a collection called Posts. with a document:

{
  _id: "5d93fddce602a10a38ad358a",
  postID: "1",
  userID: "1",
  postContent: "hello world"
}

I am able to 'join' these two with the following $lookup from Users:

{
  from: 'Posts',
  localField: 'userID',
  foreignField: 'useriD',
  as: 'usersPosts'
}

How can I write a Query to get the "postContent" of said Post? Something along the lines of:

db.Users.find($lookup :     {
      from: 'Posts',
      localField: 'userID',
      foreignField: 'useriD',
      as: 'usersPosts'
    } : userPosts[0] 

Answer 1

You can get the postContent list of each user by doing;

db.Users.aggregate([
  {
    $lookup: {
      from: "Posts",
      localField: "userID",
      foreignField: "userID",
      as: "usersPosts"
    }
  },
  {
    $addFields: {
      userPostContents: "$usersPosts.postContent"
    }
  },
  {
    $project: {
      userID: 1,
      userPostContents: 1
    }
  }
])

Where, after doing the $lookup, you already know, we can just create an array with just the postContent field from userPosts array using addFields. Converting the array of objects into an array of strings.

See code on mongoplayground

Which will get you the list of postContent per user;

[
  {
    "_id": "5d93f338e602a10a38ad3588",
    "userID": "1",
    "userPostContents": [
      "hello world"
    ]
  }
]

---

Or if you'd like to get only the postContent of the first post per user, then change your $project stage to;

{
  $project: {
    userID: 1,
    userPostContent: {
      $arrayElemAt: [
        "$userPostContents",
        0
      ]
    }
  }
}

which will get only the first postContent, like;

[
  {
    "_id": "5d93f338e602a10a38ad3588",
    "userID": "1",
    "userPostContent": "hello world"
  }
]

Check that on mongoplayground as well