I have two collections
1 Customers
{
"_id" : ObjectId("584aac38686860d502929b8b"),
"name" : "user",
"email" : "user@gmail.com"
}
2 Posts
{
"_id" : ObjectId("584aaca6686860d502929b8d"),
"title" : "Post",
"description":"description",
"user_id" : "584aac38686860d502929b8b"
}
I want to join this collection based on the user_id (from posts collection) – _id ( in customers collection).
I tried the below query:
dbo.collection('customers').aggregate([
{
$lookup:
{
from: 'posts',
localField: 'user_id',
foreignField: '_id',
as: 'posts'
}
}
])
but it’s not working
the output I am getting
{
"_id": "584aac38686860d502929b8b",
"name": "user",
"email": "user@gmail.com",
"posts": []
}
>Solution :
From attached posts collection, user_id was a string but not ObjectId.
To compare, you have to convert user_id to ObjectId first.
db.customers.aggregate([
{
$lookup: {
from: "posts",
let: {
customerId: "$_id"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
{
"$toObjectId": "$user_id"
},
"$$customerId"
]
}
}
}
],
as: "posts"
}
}
])
Note: From your existing $lookup, you have reversed localField and foreignField.
Equality Match with a Single Join Condition
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}