MongoDB join collections foreign key

Question

I have 2 collections with the following models:

User:
_id: ObjectID
name: string
org_id: string

Organization:
_id: ObjectID
name: string

I want to create a view that joins them like this:

UserView:
_id: ObjectID
name: string
org_id: string
org_name: string

I created the following lookup in Mongo Compass, which transforms the org_id into an ObjectID in the pipeline $match.

It works, but the resulting org_name is an array instead of a string.

How to make this aggregation to return the required UserView model?

{
  from: "organizations",
  let: {
    objId: {
      $toObjectId: "$org_id"
    }
  },
  pipeline: [
    {
      $match: {
        $expr: {
          $eq: [
            "$_id",
            {
              $toObjectId: "$$objId"
            }
          ]
        }
      }
    },
    {
      $project: {
        _id: 0,
        name: 1
      }
    }
  ],
  as: "org_name"
}

Answer 1

The $lookup stage returns the field as an array. You should add a field with the $set stage and get the first element of the name field with the $first operator. Note that, the second $toObjectId is unnecessary as you have converted the org_id into ObjectId type in the variable (let).

db.users.aggregate([
  {
    $lookup: {
      from: "organizations",
      let: {
        objId: {
          $toObjectId: "$org_id"
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id",
                "$$objId"
              ]
            }
          }
        },
        {
          $project: {
            _id: 0,
            name: 1
          }
        }
      ],
      as: "org_name"
    }
  },
  {
    $set: {
      org_name: {
        $first: [
          "$org_name.name"
        ]
      }
    }
  }
])

Demo @ Mongo Playground