I have a collection called store
{
"_id": {
"$oid": "64f885a4758688440f24d070"
},
"name": "Test Location",
"Tags": [
"66249849f94df523bfb6324d",
"6624984ff94df523bfb6324e"
],
"updatedAt": {
"$date": "2024-07-23T22:30:38.454Z"
}
}
And I have a Tag collection
{
"_id": {
"$oid": "66249849f94df523bfb6324d"
},
"name": "Night",
}
{
"_id": {
"$oid": "6624984ff94df523bfb6324e"
},
"name": "Bar",
}
I would like to join 2 collections with tag ids to something like
{
"_id": {
"$oid": "64f885a4758688440f24d070"
},
"name": "Test Location",
"Tags": [
"66249849f94df523bfb6324d",
"6624984ff94df523bfb6324e"
],
"TagResult": [
{"name": "Night"},
{"name": "Bar"},
]
}
Any help is appreciated.
You probably had a failed attempt with $lookup
since you have a mismatch in the datatypes of the 2 collections. (i.e. string vs ObjectId). You need to convert them to same datatype to perform the $lookup
.
db.store.aggregate([
{
"$set": {
"Tags": {
"$map": {
"input": "$Tags",
"as": "t",
"in": {
"$toObjectId": "$$t"
}
}
}
}
},
{
"$lookup": {
"from": "tag",
"localField": "Tags",
"foreignField": "_id",
"as": "TagResult"
}
}
])
Side note: if possible, refactor the schema to store the ids in the same datatype(i.e. either both ObjectIds or both strings) so you don't have to do the type conversion at run time.