MongoDB聚合管道$map返回布尔值而非目标数据的问题解决

问题描述 #

需要从SurveyQuestions集合中筛选出is_deleted为true的question条目，同时保留对应question下is_deleted为true的category_option，并且对结果排序。但使用$map的聚合查询返回了布尔值数组，无法得到目标数据。

原始数据结构 #

{"_id": "63b6c24fa14b7a7762f053f5","question": [{"type": "single","optional": false,"options": null,"is_disable": false,"is_deleted": true,"draggable": false,"choice": null,"updated_by": "6360e17547fbf84e159a3777","deleted_by": null,"_id": "63b6c3789f67afe5a4c6140d","category_option": [{"name": {"en": "bb","ar": "الخبرة الشاملة"},"optional": false,"editable": false,"sequence": 0,"draggable": true,"is_deleted": true,"updated_by": "6360e17547fbf84e159a3777","deleted_by": null,"_id": "63b6ce9674af1e81317ef2ff"}],"name": {"en": "Please rate the following items c","ar": "يرجى تقييم العناصر التالية"},"sequence": 0}]}

现有错误代码 #

const getSurvey = await SurveyQuestions.aggregate([{"$match": {"brand_id": brand_id,"experience_type_id": experience_type,"is_deleted": false,}},{"$addFields": {"question": {"$map": {"input": "$question","as": "q","in": {"$eq": ["$$q.is_deleted", true],},},},},])

当前错误结果 #

{"_id": "63b6c24fa14b7a7762f053f5","question": [true,false,false,false,false,false],"brand_id": "63b6bccbbc400713acba8fbe","experience_type_id": "63b6c24fa14b7a7762f053f3","is_disable": false,"is_deleted": false,"created_by": "6360e17547fbf84e159a3777","updated_by": "6360e17547fbf84e159a3777","deleted_by": null,"createdAt": "2023-01-05T12:27:59.741Z","updatedAt": "2023-01-06T04:25:41.547Z","__v": 0}

期望结果 #

{"_id": "63b6c24fa14b7a7762f053f5","question": [{"type": "single","optional": false,"options": null,"is_disable": false,"is_deleted": true,"draggable": false,"choice": null,"updated_by": "6360e17547fbf84e159a3777","deleted_by": null,"_id": "63b6c3789f67afe5a4c6140d","category_option": [{"name": {"en": "bb","ar": "الخبرة الشاملة"},"optional": false,"editable": false,"sequence": 0,"draggable": true,"is_deleted": true,"updated_by": "6360e17547fbf84e159a3777","deleted_by": null,"_id": "63b6ce9674af1e81317ef2ff"}],"name": {"en": "Please rate the following items c","ar": "يرجى تقييم العناصر التالية"},"sequence": 0}]}

正确解决方案 #

问题原因 #

$map的作用是转换数组中的每个元素，你当前用$eq返回布尔值，所以得到的是布尔数组。要筛选数组中符合条件的元素，应该使用$filter操作符。

代码实现 #

基础筛选（仅保留is_deleted为true的question）

const getSurvey = await SurveyQuestions.aggregate([
  // 匹配文档级别的条件
  {
    "$match": {
      "brand_id": brand_id,
      "experience_type_id": experience_type,
      "is_deleted": false
    }
  },
  // 筛选question数组中is_deleted为true的条目
  {
    "$addFields": {
      "question": {
        "$filter": {
          "input": "$question",
          "as": "q",
          "cond": {"$eq": ["$$q.is_deleted", true]}
        }
      }
    }
  },
  // 可选：对筛选后的question按sequence排序
  {
    "$addFields": {
      "question": {
        "$sortArray": {
          "input": "$question",
          "sortBy": {"sequence": 1} // 1为升序，-1为降序
        }
      }
    }
  }
])

进阶筛选（同时保留question和其下is_deleted为true的category_option）

如果需要同时筛选question中的category_option，在$filter内部对category_option再次使用$filter：

const getSurvey = await SurveyQuestions.aggregate([
  {
    "$match": {
      "brand_id": brand_id,
      "experience_type_id": experience_type,
      "is_deleted": false
    }
  },
  {
    "$addFields": {
      "question": {
        "$filter": {
          "input": "$question",
          "as": "q",
          "cond": {"$eq": ["$$q.is_deleted", true]},
          "in": {
            // 保留question的所有原始字段
            ..."$$q",
            // 筛选category_option中is_deleted为true的条目
            "category_option": {
              "$filter": {
                "input": "$$q.category_option",
                "as": "co",
                "cond": {"$eq": ["$$co.is_deleted", true]}
              }
            }
          }
        }
      }
    }
  },
  // 可选排序
  {
    "$addFields": {
      "question": {
        "$sortArray": {
          "input": "$question",
          "sortBy": {"sequence": 1}
        }
      }
    }
  }
])

说明 #

• $filter：遍历数组，仅保留满足cond条件的元素，返回符合条件的元素数组
• $sortArray：MongoDB 5.2+支持的操作符，用于对数组内的元素排序；如果使用更低版本，可以用$unwind+$sort+$group组合实现排序

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