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"
}
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"
]
}
}
}
])