MongoDB lookup and merge nested arrays

Question

I started using MongoDB after a long time, but I could not write the code to perform the following operation.

What I want to do here is to perform the lookup from the suspects and lawyers documents and merge them with the fields in the suspects field.

For now, I have performed a single lookup operation, but I could not combine it with the existing object.

[
  {
    $unwind: "$suspects",
  },
  {
    $lookup: {
      from: "suspects",
      let: {
        suspectObjId: {
          $toObjectId: "$suspects.id",
        },
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: ["$_id", "$$suspectObjId"],
            },
          },
        },
      ],
      as: "suspects",
    },
  }
]

suspects document:

[
    {
      "_id": ObjectId("64f8d65249432809cdc3cb37"),
      "fullName": "John Doe -1",
      "phoneNumber": "XXXXXXXXX",
      "totalCriminalRecord": 3
    },
    {
      "_id": ObjectId("64f8d6cd49432809cdc3cb38"),
      "fullName": "John Doe -2",
      "phoneNumber": "XXXXXXXXX",
      "totalCriminalRecord": 5
    }
  ],

lawyers document:

  [
    {
      "_id": ObjectId("64f8d58849432809cdc3cb34"),
      "fullName": "John Doe -3",
      "phoneNumber": "XXXXXXXXX",
      "workAddress": "Address of .."
    },
    {
      "_id": ObjectId("64f8d5f349432809cdc3cb35"),
      "fullName": "John Doe -4",
      "phoneNumber": "XXXXXXXXX",
      "workAddress": "Address of .."
    }
  ],

cases document:

  [
    {
      "_id": ObjectId("64f8d9f549432809cdc3cb3d"),
      "crimeNumber": 12345,
      "eventDate": "01/01/2023",
      "suspects": [
        {
          "id": "64f8d65249432809cdc3cb37",
          "note": "-- additional note --",
          "lawyer": {
            "id": "64f8d58849432809cdc3cb34",
            "note": "--additional note --"
          }
        }
      ]
    }
  ]

I want it to result as below:

{
  "_id": ObjectId("64f8d9f549432809cdc3cb3d"),
  "crimeNumber": 12345,
  "eventDate": "01/01/2023",
  "suspects": [
    {
      "id": "64f8d65249432809cdc3cb37",
      "fullName": "John Doe -1",
      "phoneNumber": "XXXXXXXXX",
      "totalCriminalRecord": 3,
      "note": "-- additional note --",
      "lawyer": {
        "id": "64f8d58849432809cdc3cb34",
        "fullName": "John Doe -3",
        "phoneNumber": "XXXXXXXXX",
        "workAddress": "Address of ..",
        "note": "--additional note --"
      }
    }
  ]
}

MongoPlayground

Answer 1

You are in the right direction. Just perform the $lookup to grab everything you need and use $mergeObjects to wrangle them.

db.cases.aggregate([
  {
    "$unwind": "$suspects"
  },
  {
    "$lookup": {
      "from": "suspects",
      "let": {
        "suspectsIds": {
          "$toObjectId": "$suspects.id"
        }
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              "$eq": [
                "$_id",
                "$$suspectsIds"
              ]
            }
          }
        },
        {
          "$unset": "_id"
        }
      ],
      "as": "suspectsLookup"
    }
  },
  {
    "$unwind": "$suspectsLookup"
  },
  {
    "$lookup": {
      "from": "lawyers",
      "let": {
        "lawyersIds": {
          "$toObjectId": "$suspects.lawyer.id"
        }
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              "$eq": [
                "$_id",
                "$$lawyersIds"
              ]
            }
          }
        },
        {
          "$unset": "_id"
        }
      ],
      "as": "lawyersLookup"
    }
  },
  {
    "$unwind": "$lawyersLookup"
  },
  {
    "$set": {
      "suspects.lawyer": {
        "$mergeObjects": [
          "$suspects.lawyer",
          "$lawyersLookup"
        ]
      }
    }
  },
  {
    "$set": {
      "suspects": [
        {
          "$mergeObjects": [
            "$suspects",
            "$suspectsLookup"
          ]
        }
      ]
    }
  },
  {
    "$unset": [
      "lawyersLookup",
      "suspectsLookup"
    ]
  }
])

Mongo Playground