Below is an aggregation with some of mid pipelines
[
{
$unwind: "$destinations"
},
{
$lookup: {
from: "customers",
localField: "destinations.sold_to_id",
foreignField: "_id",
as: "sold_to_ref"
},
},
]
As per the above query, destinations is an array which will be like
[{
type: 1,
sold_to_id: 'xxxxx'
},
{
type: 2,
sold_to_id: 'yyyy',
}
]
Now I want to perform the lookup based on the type (ie)
if type = 1, then lookup from customers
else lookup from users.
Note: customers and users are two different collections
Please help me out in this.
Thanks in advance
Edit:
The solution that I tried
[{
$unwind: "$destinations"
},
{
$lookup: {
from: "customers",
"let": {
type: "$destinations.type",
destination_id: "$destinations.sold_to_id"
},
pipeline: [
{
"$match": {
"$expr": { $and:
[
{ $eq: [ "$$type", 1 ] }, // Here I need to compare the type value with 1 which is not happening
{ $eq: [ "$_id", "$$destination_id" ] }
]
}
}
}
],
as: "sold_to_ref"
},
}]
Edit: If the input collections are like
db={
"collection": [
{
"contact": [
'adf', 'dsf', 'sdd'],
"destinations": [
{
type: 1,
sold_to_id: "xxxxx"
},
{
type: 1,
sold_to_id: "yyyy"
},
{
type: 2,
sold_to_id: "zzz"
},
{
type: 2,
sold_to_id: "www"
}
]
}
],
"customers": [
{
_id: "xxxxx",
name: "Customer1"
},
{
_id: "yyyy",
name: "Customer2"
}
],
"users": [
{
_id: "zzz",
name: "User1"
},
{
_id: "www",
name: "User2"
}
]
}
then result should be as below
[
{
"_id": ObjectId("5a934e000102030405000000"),
"contact": [
'adf', 'dsf', 'sdd'],
"destinations": [
{
type: 1,
sold_to_id: "xxxxx",
sold_to_ref: "Customer1"
},
{
type: 1,
sold_to_id: "yyyy",
sold_to_ref: "Customer2"
},
{
type: 2,
sold_to_id: "zzz",
sold_to_ref: "User1"
},
{
type: 2,
sold_to_id: "www",
sold_to_ref: "User2"
}
]
]
},
]
Workaround: You run 2 LEFT JOINS with $facet, merge them and flatten the result.
db.collection.aggregate([
{
$facet: {
customers: [
{
$addFields: {
destinations: {
$filter: {
input: "$destinations",
cond: {
$eq: [
"$$this.type",
1
]
}
}
}
}
},
{
$lookup: {
from: "customers",
localField: "destinations.sold_to_id",
foreignField: "_id",
as: "sold_to_ref"
}
},
{
$match: {
"sold_to_ref.0": {
$exists: true
}
}
}
],
users: [
{
$addFields: {
destinations: {
$filter: {
input: "$destinations",
cond: {
$eq: [
"$$this.type",
2
]
}
}
}
}
},
{
$lookup: {
from: "users",
localField: "destinations.sold_to_id",
foreignField: "_id",
as: "sold_to_ref"
}
},
{
$match: {
"sold_to_ref.0": {
$exists: true
}
}
}
]
}
},
{
$project: {
merge: {
"$concatArrays": [
"$users",
"$customers"
]
}
}
},
{
$unwind: "$merge"
},
{
"$replaceWith": {
"$mergeObjects": [
"$merge",
{
"copy": "$merge.destinations"
}
]
}
},
{
$unwind: "$copy"
},
{
$addFields: {
copy: "$$REMOVE",
sold_to_ref: {
$filter: {
input: "$sold_to_ref",
cond: {
$eq: [
"$copy.sold_to_id",
"$$this._id"
]
}
}
}
}
}
])