How to perform MongoDB aggregate lookup query for each field in a list of objects

Jimmy Prior 0
Viewed 34

I have documents that look like this:

{
    "_id": ObjectId("Some Value"),
    "example_list" : [
        {
            "external_id" : ObjectId("Some Value1"),
            "other" : "stuff"
        },
        {
            "external_id" : ObjectId("Some Value2"),
            "other" : "stuff"
        },
        {
            "external_id" : ObjectId("Some Value3"),
            "other" : "stuff"
        }
    ]
}

I want to resolve the external_id (using a lookup) from a collection named example_collection for each object in the example_list so that the output resembles this:

{
    "_id": ObjectId("Some Value"),
    "example_list" : [
        {
            "external_id" : ObjectId("Some Value1"),
            "other" : "stuff",
            "external_data" : "data1 retrieved from lookup"
        },
        {
            "external_id" : ObjectId("Some Value2"),
            "other" : "stuff",
            "external_data" : "data2 retrieved from lookup"
        },
        {
            "external_id" : ObjectId("Some Value3"),
            "other" : "stuff",
            "external_data" : "data3 retrieved from lookup"
        }
    ]
}

How can I accomplish this with an aggregate pipeline? I know how to do a lookup but not how to iterate over each object in the list and insert back into the object.

aggregation-framework pymongo mongodb-query mongodb
1 Answers

One option is:

db.example_lists.aggregate([
  {$lookup: {
      from: "example_collection",
      localField: "example_list.external_id",
      foreignField: "_id",
      as: "example_list2"
  }},
  {
    $set: {
      example_list2: "$$REMOVE",
      example_list: {$map: {
          input: "$example_list",
          in: {$mergeObjects: [
              "$$this",
              {$arrayElemAt: [
                  "$example_list2",
                  {$indexOfArray: ["$example_list2._id", "$$this.external_id"]}
                ]
              }
            ]
          }
        }
      }
    }
  }
])

See how it works on the playground example

If you want to remove the external_id from the results, you can add a $map step in the middle:

{$set: {
     example_list: {
       $map: {
         input: "$example_list",
         in: {_id: "$$this.external_id", other: "$$this.other"}
       }
     }
   }
 },

See how it works on the playground example

nimrod serok0
Related