MongoDB聚合$addFields中多层嵌套过滤实现方案咨询

数据库集合数据 #

[{
  "value": {
    "shipmentId": 1079,
    "customer_orders": [
      {
        "customer_order_id": 1124,
        "active": false
      },
      {
        "customer_order_id": 1277,
        "active": true,
        "items": [
          {
            "item_id": 281,
            "active": false,
            "qty": 1,
            "name": "apples",
            "attributes": null
          },
          {
            "item_id": 282,
            "active": true,
            "qty": 2,
            "name": "bananas"
          }
        ]
      }
    ],
    "carrier_orders": [
      {
        "carrier_order_id": 744,
        "active": true
      }
    ]
  }
}]

尝试的查询 #

db.getCollection('shipments').aggregate([
  {
    "$match": {
      "value.shipmentId": {
        "$in": [
          1079
        ]
      }
    }
  },
  {
    "$project": {
      "value.shipmentId": 1,
      "value.customer_orders": 1,
      "value.carrier_orders": 1,
    }
  },
  {
      "$addFields":{
          "value.customer_orders":{
              $filter:{
                    input: "$value.customer_orders",
                    as: "customer_order",
                    cond: {
                        $eq: ["$$customer_order.active", true]
                    }
              }
          },
          "value.customer_orders.items":{
              $filter:{
                    input: "$value.customer_orders.items",
                    as: "item",
                    cond: {
                        $eq: ["$$item.active", true]
                    }
              }
          },
          "value.carrier_orders": {
              $filter:{
                    input: "$value.carrier_orders",
                    as: "carrier_order",
                    cond: {
                        $eq: ["$$carrier_order.active", true]
                    }
              }
          }
      }
  }
]);

期望输出 #

[{
  "value": {
    "shipmentId": 1079,
    "customer_orders": [
      {
        "customer_order_id": 1277,
        "active": true,
        "items": [
          {
            "item_id": 282,
            "active": true,
            "qty": 2,
            "name": "bananas"
          }
        ]
      }
    ],
    "carrier_orders": [
      {
        "carrier_order_id": 744,
        "active": true
      }
    ]
  }
}]

需求说明 #

需要在两个层级上应用过滤：

• 过滤value.customer_orders中active为false的订单
• 在保留的活跃客户订单中，过滤items里active为false的商品
  同时要保留客户订单层级的所有属性，如何通过MongoDB聚合管道实现这种多层嵌套条件过滤？

解决方案 #

你的问题在于尝试直接修改value.customer_orders.items时，此时value.customer_orders已经是过滤后的数组，无法直接通过层级引用处理内部的items数组。需要在过滤客户订单的同时，用$map配合$filter对每个订单的items进行过滤：

分阶段实现版本 #

db.getCollection('shipments').aggregate([
  {
    "$match": {
      "value.shipmentId": { "$in": [1079] }
    }
  },
  {
    "$addFields": {
      // 先过滤出活跃的客户订单
      "value.customer_orders": {
        "$filter": {
          "input": "$value.customer_orders",
          "as": "customer_order",
          "cond": { "$eq": ["$$customer_order.active", true] }
        }
      },
      // 过滤活跃的承运商订单
      "value.carrier_orders": {
        "$filter": {
          "input": "$value.carrier_orders",
          "as": "carrier_order",
          "cond": { "$eq": ["$$carrier_order.active", true] }
        }
      }
    }
  },
  {
    "$addFields": {
      // 对过滤后的客户订单，再处理内部的items数组
      "value.customer_orders": {
        "$map": {
          "input": "$value.customer_orders",
          "as": "order",
          "in": {
            // 保留订单的所有原有字段
            "$$order",
            // 覆盖items字段，只保留活跃商品
            "items": {
              "$filter": {
                "input": "$$order.items",
                "as": "item",
                "cond": { "$eq": ["$$item.active", true] }
              }
            }
          }
        }
      }
    }
  }
]);

合并阶段版本 #

db.getCollection('shipments').aggregate([
  {
    "$match": {
      "value.shipmentId": { "$in": [1079] }
    }
  },
  {
    "$addFields": {
      "value.customer_orders": {
        "$map": {
          // 先嵌套过滤出活跃的客户订单
          "input": {
            "$filter": {
              "input": "$value.customer_orders",
              "as": "customer_order",
              "cond": { "$eq": ["$$customer_order.active", true] }
            }
          },
          "as": "order",
          "in": {
            // 保留原订单所有属性，同时过滤items
            "$$order",
            "items": {
              "$filter": {
                "input": "$$order.items",
                "as": "item",
                "cond": { "$eq": ["$$item.active", true] }
              }
            }
          }
        }
      },
      "value.carrier_orders": {
        "$filter": {
          "input": "$value.carrier_orders",
          "as": "carrier_order",
          "cond": { "$eq": ["$$carrier_order.active", true] }
        }
      }
    }
  }
]);

两种方式都能实现需求：先筛选出活跃的客户订单，再对每个订单内的商品进行活跃筛选，同时完整保留订单的所有原属性。

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