Compound key lookup not working as intended in mongoDb

Question

I want to perform join on the basis of orderNo and articleCode under details (in both orderList and orderHistory) using aggregation

orderList: {
  "orderNo": "0000004680",
  "details": [{
      "articleCode": "000000000910000130",
      "pgiStatus": " ",
      "fulfillmentStatus": "Y",
      "shortCloseStatus": "Y"
    },
    {
      "articleCode": "000000000910000131",
      "pgiStatus": " ",
      "shortCloseQty": "0",
      "fulfillmentStatus": "Y",
    }
  ]
}

orderHistory: {
  "orderNo": "0000004680",
  "orderHistoryStatus": "COM",
  "details": [{
    "shipmentStatus": "SHF",
    "articleCode": "000000000910000130",
    "billingDocNo": "0000123"
  }]
}

Below is the query which I have written :

let query = [{
    $match: {
      orderNo,
      "createdBy": userId
    }
  },
  {
    $unwind: "$details"
  },
  {
    $lookup: {
      from: "jed_order_history",
      let: {
        orderNo: "$orderNo",
        articleCode: "$details.articleCode"
      },
      pipeline: [{
        $match: {
          $expr: {
            $and: [{
                $eq: ["$orderNo", "$$orderNo"]
              },
              {
                $eq: ["$details.articleCode", "$$articleCode"]
              }
            ]
          }
        }
      }],
      as: "orderHistory"
    }
  },
  {
    $unwind: {
      path: "$orderHistory",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $lookup: {
      from: "jed_orderShipment",
      localField: "orderHistory.details.billingDocNo",
      foreignField: "billingNumber",
      as: "orderShipment"
    }
  },
  {
    $project: {
      orderNo: 1,
      createdAt: 1,
      paymentAccountingStatus: 1,
      orderListDetails: "$details",
      orderHistory: {
        $ifNull: ["$orderHistory", {}]
      },
      orderShipment: {
        $ifNull: [{
          $arrayElemAt: ["$orderShipment", 0]
        }, {}]
      }
    }
  }
];

Here, in the output, both the orderHistory and orderShipment are appearing as empty objects, even though the matching data is present.

When I am removing this part { $eq: ["$details.articleCode", "$$articleCode"] }, then I am getting the expected data but I have to search both on the basis of orderNo and articleCode under the details field.

Answer 1

First, I think you made a typo from: "jed_order_history" should be from: "orderHistory", Second I think you need to unwind details in the lookup pipeline before performing the match. because this is necessary as the details are in an array and you need to compare each item individually

let query = [
    { 
        $match: { 
            orderNo,
            "createdBy": userId
        } 
    },
    { 
        $unwind: "$details" 
    },
    {
        $lookup: {
            from: "orderHistory",
            let: { localOrderNo: "$orderNo", localArticleCode: "$details.articleCode" },
            pipeline: [
                { $unwind: "$details" },
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: ["$orderNo", "$$localOrderNo"] },
                                { $eq: ["$details.articleCode", "$$localArticleCode"] }
                            ]
                        }
                    }
                }
            ],
            as: "orderHistory"
        }
    },    
    { 
        $unwind: { 
            path: "$orderHistory", 
            preserveNullAndEmptyArrays: true 
        } 
    },
    {
        $lookup: {
            from: "orderShipment",
            localField: "orderHistory.details.billingDocNo",
            foreignField: "billingNumber",
            as: "orderShipment"
        }
    },
    {
        $project: {
            orderNo: 1,
            createdAt: 1,
            paymentAccountingStatus: 1,
            orderListDetails: "$details",
            orderHistory: { $ifNull: ["$orderHistory", {}] },
            orderShipment: { $ifNull: [{ $arrayElemAt: ["$orderShipment", 0] }, {}] }
        }
    }
];