MongoDB $lookup replace array of _id with array of objects without converting arrays to object and removing fields

Question

I have an object like this:

{
"_id": {
    "$oid": "5f0047f02fd3fc048aab9ee9"
},
"array": [
    {
        "_id": {
            "$oid": "5f00dcc23e12b8721e4f3672"
        },
        "name": "NAME",
        "sub_array": [
            {
                "sub_array2": [
                    {
                        "$oid": "5f00e367f7b8747beddc6d31"
                    },
                    {
                        "$oid": "5f00f26c1facd18c5158d1d3"
                    }
                ],
                "_id": {
                    "$oid": "5f00de99a8802e767885e72b"
                },
                "week_day": 1
            },
            {
                "sub_array2": [
                    {
                        "$oid": "5f00e367f7b8747beddc6d31"
                    }
                ],
                "_id": {
                    "$oid": "5f00f2501facd18c5158d1d2"
                },
                "week_day": 3
            }
        ]
    },
    {
        "_id": {
            "$oid": "5f00f2401facd18c5158d1d1"
        },
        "name": "NAME1",
        "sub_array": []
    }
]
}

I want to replace sub_array ids with objects from another collection but that results converting array and sub_array to objects and losing all of the data like week_day. Lookup:

 '$lookup': {
  'from': 'sati', 
  'localField': 'array.sub_array.sub_array2', 
  'foreignField': '_id', 
  'as': 'array.sub_array.sub_array2'
}

Result:

{
"_id": {
    "$oid": "5f0047f02fd3fc048aab9ee9"
},
"array": {
    "sub_array": {
        "sub_array2": [
            {
                "_id": {
                    "$oid": "5f00e367f7b8747beddc6d31"
                },
                "endTime": "2020-07-03T12:06:50+0000",
                "startTime": "2020-07-03T12:05:50+0000",
                "data1": {
                    "$oid": "5f005e63ab1cbf2374d5163f"
                }
            },
            {
                "_id": {
                    "$oid": "5f00e367f7b8747beddc6d31"
                },
                "endTime": "2020-07-03T12:06:50+0000",
                "startTime": "2020-07-03T12:05:50+0000",
                "data1": {
                    "$oid": "5f005e63ab1cbf2374d5163f"
                }
            },
            {
                "_id": {
                    "$oid": "5f00e367f7b8747beddc6d31"
                },
                "endTime": "2020-07-03T12:06:50+0000",
                "startTime": "2020-07-03T12:05:50+0000",
                "data1": {
                    "$oid": "5f005e63ab1cbf2374d5163f"
                }
            }
        ]
    }
}
}

Is there a way to "replace" the individual ids without converting entire arrays to objects and removing other fields. I know mongoose can do that but I'm not permitted to use it. None of the other questions helped (example).

Valijon · Accepted Answer · 2020-07-05 11:40:18Z

It will override entire object key:value with $lookup result. Instead, store the lookup result in the sati variable and add an extra stage like shown below.

$map allows use iterate over an array and transform each item.

db.collection.aggregate([
  {
    "$lookup": {
      "from": "sati",
      "localField": "array.sub_array.sub_array2",
      "foreignField": "_id",
      "as": "sati"
    }
  },
  {
    $project: {
      array: {
        $map: {
          input: "$array",
          as: "array",
          in: {
            _id: "$$array._id",
            name: "$$array.name",
            sub_array: {
              $map: {
                input: "$$array.sub_array",
                as: "sub_array",
                in: {
                  _id: "$$sub_array._id",
                  week_day: "$$sub_array.week_day",
                  sub_array2: {
                    $filter: {
                      input: "$sati",
                      as: "sati_item",
                      cond: {
                        $in: [
                          "$$sati_item._id",
                          "$$sub_array.sub_array2"
                        ]
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

MongoPlayground | Altenative with $mergeObjects

Collectives™ on Stack Overflow

MongoDB $lookup replace array of _id with array of objects without converting arrays to object and removing fields

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related