How to reverse $unwind or re-assemble after $lookup?

Question

I´ve been trying to reverse $unwind in nested array. Please, if you could help me it would be great. Thanks in advance.

Here are the details:

checklists collection, this collection has steps and each step has many areas, and I'd like to lookup to fill the area by id. I did it but I cannot reverse $unwind.

{
    "steps": [{
        "name": "paso1",
        "description": "paso1",
        "estimated_time": 50,
        "active": true,
        "areas": [{
            "area_id": "60b6e728c44f0365c0d547d6"
        }, {
            "area_id": "60b6e7a2c44f0365c0d547d8"
        }]
    }, {
        "name": "paso2",
        "description": "o",
        "estimated_time": 7,
        "active": true,
        "areas": [{
            "area_id": "60b6e76ac44f0365c0d547d7"
        }]
    }, {
        "name": "paso2",
        "description": "l",
        "estimated_time": 7,
        "active": true,
        "areas": [{
            "area_id": "60b6e728c44f0365c0d547d6"
        }]
    }],
    "name": "prueba",
    "description": "prueba",
    "type": "prueba",
    "active": true,
    "updated_at": {
        "$date": "2021-06-02T23:56:02.232Z"
    },
    "created_at": {
        "$date": "2021-06-01T22:44:57.114Z"
    },
    "__v": 0
}

area collection

{
    "_id":"60b6e706c44f0365c0d547d5"
    "name": "Development",
    "short_name": "DEV",
    "description": "Development area",
    "updated_at": {
        "$date": "2021-06-02T02:03:50.383Z"
    },
    "created_at": {
        "$date": "2021-06-02T02:03:50.383Z"
    },
    "__v": 0,
    "active": true
}

My aggregation

db.checklists.aggregate([
    {
        "$unwind": "$steps"
    },
    {
        "$unwind": "$steps.areas"
    },
    {
        "$lookup": {
            "from": "areas",
            "let": {
                "area_id": {
                    "$toObjectId": "$steps.areas.area_id"
                }
            },
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            "$eq": [
                                "$_id",
                                "$$area_id"
                            ]
                        }
                    }
                }
            ],
            "as": "convertedItems"
        }
    },
    {
        "$group": {
            "_id": "$steps.name",
            "root": {
                "$first": "$$ROOT"
            },
            "items": {
                "$push": {
                    "$mergeObjects": [
                        "$steps.areas",
                        {
                            "$arrayElemAt": [
                                "$convertedItems",
                                0
                            ]
                        }
                    ]
                }
            },
        }
    },
    {
        "$addFields": {
            "values": {
                "$reduce": {
                    "input": "$items",
                    "initialValue": [],
                    "in": {
                        "$concatArrays": [
                            "$$value",
                            {
                                "$cond": [
                                    {
                                        "$in": [
                                            "$$this.area_id",
                                            "$$value.area_id"
                                        ]
                                    },
                                    [],
                                    [
                                        "$$this"
                                    ]
                                ]
                            }
                        ]
                    }
                }
            }
        }
    },
    {
        "$addFields": {
            "root.steps.areas": "$values"
        }
    },
    {
        "$replaceRoot": {
            "newRoot": "$root"
        }
    },
    {
        "$group": {
            "_id": "$_id",
            "root": {
                "$first": "$$ROOT"
            },
            "steps": {
                "$push": "$steps"
            }
        }
    },
    {
        "$addFields": {
            "root.steps": "$steps"
        }
    },
    {
        "$replaceRoot": {
            "newRoot": "$root"
        }
    },
    {
        "$project": {
            "convertedItems": 0
        }
    }
])

I don´t get to form this output:

 {
        "steps": [{
            "name": "paso1",
            "description": "paso1",
            "estimated_time": 50,
            "active": true,
            "areas": [{
                "_id": "60b6e728c44f0365c0d547d6",
                "name":"Development",
                ..... //join or lookup
            }, {
                "_id": "60b6e7a2c44f0365c0d547d8",
                "name":"Development",
                ..... //join or lookup
            }]
        }],
        "name": "prueba",
        "description": "prueba",
        "type": "prueba",
        "active": true,
        "updated_at": {
            "$date": "2021-06-02T23:56:02.232Z"
        },
        "created_at": {
            "$date": "2021-06-01T22:44:57.114Z"
        },
        "__v": 0
    }

Thank you very much!

user_8987128 · Answer

$unwind deconstruct steps array
$lookup with areas collection pass area_id in let
$match to check is _id in area_ids after converting to string
$project to show required fields
$group by _id and reconstruct the steps array and pass your required fields

db.checklists.aggregate([
  { $unwind: "$steps" },
  {
    $lookup: {
      from: "areas",
      let: { area_id: "$steps.areas.area_id" },
      pipeline: [
        {
          $match: {
            $expr: { $in: [{ $toString: "$_id" }, "$$area_id"] }
          }
        },
        { $project: { name: 1 } }
      ],
      as: "steps.areas"
    }
  },
  {
    $group: {
      _id: "$_id",
      steps: { $push: "$steps" },
      name: { $first: "$name" },
      description: { $first: "$description" },
      type: { $first: "$type" },
      active: { $first: "$active" },
      updated_at: { $first: "$updated_at" },
      created_at: { $first: "$created_at" },
      __v: { $first: "$__v" }
    }
  }
])

Playground

How to reverse $unwind or re-assemble after $lookup?

1 Answers