mongodb - 如何使用文档中的 2 个数组映射数组

Question

我正在尝试在文档中映射 2 个数组。我有一个名为 headers 的数组我想用子数组映射，customData , 在名为 list 的父数组中.
我试图将 key 设置为 fieldName来自 headers匹配使用 _id参数与相应的 field_id来自 customData在 list大批。我还想排除 customData 中的任何项目哪里systemDelete_DT不是 null .
我试过使用 $map和 $filter但没有得到任何地方!
我如何像下面的示例一样重新映射数组？
Playground Attempt
我的尝试:(

db.collection.aggregate([
  {
    $unwind: "$list"
  },
  {
    $match: {
      $and: [
        {
          group: ObjectId("6099614e503dd75c29c9715b")
        },
        {
          systemDelete_DT: null
        },
        {
          "list.systemDelete_DT": null
        }
      ]
    }
  },
  {
    $project: {
      _id: 0,
      text: "$list.text"
    }
  }
])

预期输出

[
  {
    "text": "abc1",
    "Field1": "Example1", 
    "Field2": "Example2"
  },
  {
    "text": "abc2"
    "Field1": "Example3"
    "Field2": "Example4"
  }
]

示例文档

[
  {
    "_id": ObjectId("6099614e503dd75c29c9715d"),
    "systemDelete_DT": null,
    "group": ObjectId("6099614e503dd75c29c9715b"),
    "headers": [
      {
        "_id": ObjectId("609e875203041759d7b0428a"),
        "systemDelete_DT": "2021-05-14T15:21:06+01:00",
        "fieldName": "Field1-X"
      },
      {
        "_id": ObjectId("609fe89e5d4ac02438abdc31"),
        "systemDelete_DT": null,
        "fieldName": "Field1"
      },
      {
        "_id": ObjectId("60a00c14543d582c151e34f6"),
        "systemDelete_DT": null,
        "fieldName": "Field2"
      }
    ],
    "list": [
      {
        "_id": ObjectId("609966bccce7575f2408fddc"),
        "systemDelete_DT": null,
        "text": "abc1",
        "customData": [
          {
            "_id": ObjectId("609e875203041759d7b0428b"),
            "systemDelete_DT": "2021-05-15T19:39:20+01:00",
            "field_id": ObjectId("609e875203041759d7b0428a"),
            "fieldText": "Example-X"
          },
          {
            "_id": ObjectId("609fe89e5d4ac02438abdc32"),
            "systemDelete_DT": null,
            "field_id": ObjectId("609fe89e5d4ac02438abdc31"),
            "fieldText": "Example1"
          },
          {
            "_id": ObjectId("60a00c14543d582c151e34f7"),
            "systemDelete_DT": null,
            "field_id": ObjectId("60a00c14543d582c151e34f6"),
            "fieldText": "Example2"
          }
        ]
      },
      {
        "_id": ObjectId("609966becce7575f2408fdde"),
        "systemDelete_DT": null,
        "text": "abc2",
        "customData": [
          {
            "_id": ObjectId("609e875203041759d7b0428b"),
            "systemDelete_DT": "2021-05-15T19:39:20+01:00",
            "field_id": ObjectId("609e875203041759d7b0428a"),
            "fieldText": "Example-X"
          },
          {
            "_id": ObjectId("609fe89e5d4ac02438abdc32"),
            "systemDelete_DT": null,
            "field_id": ObjectId("609fe89e5d4ac02438abdc31"),
            "fieldText": "Example3"
          },
          {
            "_id": ObjectId("60a00c14543d582c151e34f7"),
            "systemDelete_DT": null,
            "field_id": ObjectId("60a00c14543d582c151e34f6"),
            "fieldText": "Example4"
          }
        ]
      },
      {
        "_id": ObjectId("609966d2474ad05f369c891a"),
        "systemDelete_DT": "2021-05-14T15:21:06+01:00",
        "text": "abc32",
        "customData": [
          {
            "_id": ObjectId("609e875203041759d7b0428b"),
            "systemDelete_DT": "2021-05-15T19:39:20+01:00",
            "field_id": ObjectId("609e875203041759d7b0428a"),
            "fieldText": ""
          },
          {
            "_id": ObjectId("609fe89e5d4ac02438abdc32"),
            "systemDelete_DT": null,
            "field_id": ObjectId("609fe89e5d4ac02438abdc31"),
            "fieldText": "Example5"
          },
          {
            "_id": ObjectId("60a00c14543d582c151e34f7"),
            "systemDelete_DT": null,
            "field_id": ObjectId("60a00c14543d582c151e34f6"),
            "fieldText": "Example6"
          }
        ]
      },
      
    ]
  }
]

Answer 1

您可以使用如下

$unwind解构数组

$addFields或 $project可以用

$map遍历数组并选择必要的字段

$mergeObject合并 fieldName与 customeData阵列

$filter根据条件过滤数组

这是代码

db.collection.aggregate([
  {
    $unwind: "$list"
  },
  {
    $addFields: {
      "list.customData": {
        $filter: {
          input: "$list.customData",
          cond: {
            $eq: [
              "$$this.systemDelete_DT",
              null
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      "list.customData": {
        $map: {
          input: "$list.customData",
          as: "cData",
          in: {
            "$mergeObjects": [
              "$$cData",
              {
                $arrayElemAt: [
                  {
                    $map: {
                      input: {
                        $filter: {
                          input: "$headers",
                          as: "head",
                          cond: {
                            $eq: [
                              "$$cData.field_id",
                              "$$head._id"
                            ]
                          }
                        }
                      },
                      in: {
                        fieldName: "$$this.fieldName"
                      }
                    }
                  },
                  0
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$list"
    }
  },
  {
    $unwind: "$customData"
  },
  {
    "$replaceRoot": {
      "newRoot": "$customData"
    }
  }
])

工作Mongo playground