MongoDB聚合中基于orderNo与articleCode的复合键关联失效

问题描述 #

需要通过MongoDB聚合操作，基于orderNo以及orderList和orderHistory两个集合中details字段下的articleCode进行复合键关联查询。

集合结构示例 #

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"
  }]
}

编写的聚合查询语句 #

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]
        }, {}]
      }
    }
  }
];

问题现象 #

加入{ $eq: ["$details.articleCode", "$$articleCode"] }条件后，查询结果中的orderHistory和orderShipment均为空对象，但实际存在匹配数据；移除该条件后可正常获取预期数据，但业务要求必须基于orderNo和details.articleCode进行复合键关联。

解决方案 #

问题核心：orderHistory集合中的details是数组字段，直接用$details.articleCode与单个字符串值$$articleCode做相等比较时，数组和字符串永远不匹配，导致lookup无结果。

修正后的聚合查询 #

以下是两种可行的修正方式，均能实现orderNo+details.articleCode的复合键关联：

方式一：使用$in匹配数组中的articleCode

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"] },
              // 提取orderHistory.details中的所有articleCode组成数组，判断目标值是否在其中
              { $in: ["$$articleCode", { $map: { input: "$details", as: "d", in: "$$d.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]
        }, {}]
      }
    }
  }
];

方式二：使用$anyElementTrue判断数组中存在匹配元素

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"] },
              // 遍历orderHistory.details数组，判断是否有元素的articleCode匹配目标值
              {
                $anyElementTrue: {
                  $map: {
                    input: "$details",
                    as: "histDetail",
                    in: { $eq: ["$$histDetail.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]
        }, {}]
      }
    }
  }
];

可选优化：精准匹配单个明细项 #

如果需要仅保留orderHistory中匹配的details项（而非整个数组），可以在lookup的pipeline中先拆分数组再匹配，最后重组结构：

let query = [{
    $match: {
      orderNo,
      "createdBy": userId
    }
  },
  {
    $unwind: "$details"
  },
  {
    $lookup: {
      from: "jed_order_history",
      let: {
        orderNo: "$orderNo",
        articleCode: "$details.articleCode"
      },
      pipeline: [
        { $match: { $expr: { $eq: ["$orderNo", "$$orderNo"] } } },
        { $unwind: "$details" },
        { $match: { $expr: { $eq: ["$details.articleCode", "$$articleCode"] } } },
        // 重组orderHistory文档结构
        {
          $group: {
            _id: "$_id",
            orderNo: { $first: "$orderNo" },
            orderHistoryStatus: { $first: "$orderHistoryStatus" },
            details: { $push: "$details" }
          }
        }
      ],
      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]
        }, {}]
      }
    }
  }
];

内容的提问来源于stack exchange，提问作者Atul Kumar