How to map an array using 2 arrays in a document

Question

I'm attempting to map 2 arrays within a document. I've got an array called headers that I want to map with a sub array, customData, within a parent array called list.

I am trying to set the key as the fieldName from the headers matching that using the _id parameter with the respective field_id from customData in the list array. I also want to exclude any items in customData where systemDelete_DT is not null.

I've tried using $map and $filter but not got any where!

How do i re-map the array like my example below?

Playground Attempt

My attempt :(

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

Expected Output

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

Example Document

[
  {
    "_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"
          }
        ]
      },
      
    ]
  }
]

user_7975771 · Answer

You can use like following

$unwind to deconstruct the array
$addFields or $project can be used
$map to go through the array and select necessary field
$mergeObject to merge the fieldName with the customeData array
$filter to filter the array based on the condition

Here is the code

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

Working Mongo playground

How to map an array using 2 arrays in a document

1 Answers