Here is the updated query that successfully executed
db.indents.aggregate([
{
$project: {
_id: 1,
destinations: 1,
contact_details: 1,
contacts_gt_1: {
$gt: [
{
$size: {
$cond: [
{ $isArray: "$contact_details" },
"$contact_details",
[]
]
}
},
0
]
}
}
},
{
$match: {
contacts_gt_1: true,
}
},
{
$project: {
_id: 1,
destinations: 1,
contact_details: 1,
}
},
{
$unwind: "$destinations"
},
{
$lookup: {
from: "customers",
localField: "destinations.sold_to_id",
foreignField: "_id",
as: "sold_to_ref"
},
},
{
$unwind: "$sold_to_ref"
},
{
$lookup: {
from: "customers",
localField: "destinations.ship_to_id",
foreignField: "_id",
as: "ship_to_ref"
}
},
{
$unwind: "$ship_to_ref"
},
{
$project: {
_id: 1,
destinations: 1,
contact_details: 1,
ship_to_ref: "$ship_to_ref.reference_id",
sold_to_ref: "$sold_to_ref.reference_id",
}
},
{
$unwind: "$contact_details"
},
{
$group: {
_id: {
ship_to_ref: "$contact_details.ship_to_ref",
sold_to_ref: "$contact_details.sold_to_ref",
contact_email: "$contact_details.contact_email",
},
orderData: {
$addToSet: {
_id: "$_id",
destinations: "$destinations",
},
},
},
},
{
$unwind: "$orderData"
},
{
$group: {
_id: {
order_id: "$orderData._id",
entry: "$_id",
},
destinations: {
$push: "$orderData.destinations"
},
}
},
{
$group: {
_id: "$_id.entry",
orders: {
$push: {
order_id: "$_id.order_id",
destinations: "$destinations"
}
}
}
},
])