db={"orders":[{id:"123456789",merchantId:"abc",createdAt:"2022-03-10T15:18:53.615Z",items:[{name:"aaa",externalCode:"123",quantity:1,options:[{name:"ccc",externalCode:"999",quantity:2,}],},{name:"bbb",externalCode:"456",quantity:2,options:[{name:"aaa",externalCode:"123",quantity:5}]},{name:"ddd",externalCode:"789",quantity:2,options:[]}]}],"incomings":[{merchantId:"abc",date:"2022-02-17T10:00:00.000+00:00",product:{code:"123",name:"aaa",quantity:24,}},{merchantId:"abc",date:"2022-02-16T10:00:00.000+00:00",product:{code:"123",name:"aaa",quantity:10,}},{merchantId:"abc",date:"2022-03-17T10:00:00.000+00:00",product:{code:"123",name:"aaa",quantity:24,}}]}

db.orders.aggregate([{"$match":{merchantId:"abc"}},{"$project":{items:{"$map":{"input":"$items","as":"i","in":{name:"$$i.name",externalCode:"$$i.externalCode",quantity:"$$i.quantity",date:"$createdAt"}}},options:{"$reduce":{"input":"$items","initialValue":[],"in":{"$concatArrays":["$$value",{"$reduce":{"input":"$$this.options","initialValue":[],"in":{"$concatArrays":["$$value",[{name:"$$this.name",externalCode:"$$this.externalCode",quantity:"$$this.quantity",date:"$createdAt"}]]}}}]}}}}},{"$project":{union:{"$setUnion":["$items","$options"]}}},{"$unwind":"$union"},{$group:{_id:"$union.externalCode",name:{$first:"$union.name"},externalCode:{$first:"$union.externalCode"},date:{$first:"$union.date"},quantity:{$first:"$union.quantity"}}},{"$lookup":{"from":"incomings",let:{extCode:"$externalCode",date:"$date",},pipeline:[{$match:{merchantId:"abc"}},{"$match":{$expr:{$and:[{$eq:["$product.code","$$extCode"]},{$lte:["$date","$$date"]}]}}}],"as":"lookupResult"}},{"$unwind":"$lookupResult"},{$project:{externalCode:"$externalCode",name:"$name",orderDate:"$date",orderQuantity:"$quantity",incomingDate:"$lookupResult.date",incomingQuantity:"$lookupResult.product.quantity"}}])