Below is the expected query
`
db.external_S_P_FLAT_main_api.aggregate([
{
$unionWith: {
coll: "external_S_C_FLAT_main_api"
}
},
{
"$lookup": {
from: "external_S_C_FLAT_main_api",
let: {
let_data__pricing__cost_center: {
"$getField": "data.pricing.cost_center"
}
},
pipeline: [
{
"$match": {
"$expr": {
"$and": [
{
"$eq": [
{
"$getField": "data.costcenter.cost_center"
},
"$$let_data__pricing__cost_center"
]
}
]
}
}
}
],
as: "from_external_S_C_FLAT_main_api"
}
},
{
"$addFields": {
"otherId": {
"$cond": [
{
"$eq": [
{
$getField: "from_external_S_C_FLAT_main_api"
},
[]
]
},
{
"$toString": "$_id"
},
{
"$toString": {
$arrayElemAt: [
"$from_external_S_C_FLAT_main_api._id",
0
]
}
},
]
}
}
},
{
"$unwind": {
path: "$from_external_S_C_FLAT_main_api",
preserveNullAndEmptyArrays: true
}
},
{
"$replaceRoot": {
newRoot: {
"$mergeObjects": [
"$from_external_S_C_FLAT_main_api",
"$$ROOT"
]
}
}
},
{
"$lookup": {
from: "external_S_P_FLAT_main_api",
let: {
let_data__costcenter__cost_center: {
"$getField": "data.costcenter.cost_center"
}
},
pipeline: [
{
"$match": {
"$expr": {
"$and": [
{
"$eq": [
{
"$getField": "data.pricing.cost_center"
},
"$$let_data__costcenter__cost_center"
]
}
]
}
}
}
],
as: "from_external_S_P_FLAT_main_api"
}
},
{
$group: {
_id: "$otherId",
duplicates: {
$push: "$$ROOT"
}
}
},
{
$project: {
result: {
$arrayElemAt: [
"$duplicates",
0
]
}
}
},
{
"$unwind": {
path: "$result",
preserveNullAndEmptyArrays: true
}
},
{
"$replaceRoot": {
newRoot: {
"$mergeObjects": [
"$result",
"$$ROOT"
]
}
}
},
{
"$project": {
_id: 0,
"external_S_P_FLAT_main_api_data.pricing.controlling_area": 0,
"external_S_P_FLAT_main_api_data.pricing.cost_center": 0,
external_S_P_FLAT_main_api_IS_INVALID: 0,
external_S_P_FLAT_main_api_USER_ID: 0,
from_external_S_C_FLAT_main_api: 0,
from_external_S_P_FLAT_main_api: 0,
result: 0,
otherId: 0
}
}
])
`