Can I update a list in a collection using data from another collection in mongo?

Question

I have 2 collections, Offers and Inventory

Offers

...
{
    "_id": "<someid>",
    "name": "Offer1",
    "items": [
        {
            "id": "1234",
            "itemName": "Apples"
        },
        {
            "id": "2345",
            "itemName": "Oranges"
        }
    ]
},
{
    "_id": "<someid>",
    "name": "Offer2",
    "items": [
        {
            "id": "1234",
            "itemName": "Apples"
        },
        {
            "id": "3456",
            "itemName": "Bananas"
        }
    ]
},
...

Inventory

...
{
    "_id": "<someid>",
    "id": "1234",
    "shortName": "Apples",
    "longName": "Malus domestica"
},
...

I would like to update the itemName in the Offers collection with the longName of the Inventory. Is it possible to do so directly in mongodb? Alternatively is there a simple way to do this in python?

Answer 1

Here's a couple ways to do it. Besides these two ways, there may be a better way.

N.B.: I couldn't fully test these so you may want to try on a backup collection and verify functionality.

A "$lookup" followed by a giant "$map". And then "$merge".

db.Offers.aggregate([
  {
    "$lookup": {
      "from": "Inventory",
      "localField": "items.id",
      "foreignField": "id",
      "pipeline": [
        {
          "$project": {
            "_id": 0,
            "id": 1,
            "longName": 1
          }
        }
      ],
      "as": "longNames"
    }
  },
  {
    "$set": {
      "items": {
        "$map": {
          "input": "$items",
          "as": "item",
          "in": {
            "$mergeObjects": [
              "$$item",
              {
                "itemName": {
                  "$getField": {
                    "field": "longName",
                    "input": {
                      "$first": {
                        "$filter": {
                          "input": "$longNames",
                          "as": "elem",
                          "cond": { "$eq": [ "$$item.id", "$$elem.id" ] }
                        }
                      }
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  { "$unset": "longNames" },
  { "$merge": "Offers" }
])

Try it on mongoplayground.net.

An "$unwind" followed by a "$lookup", updating "items.itemName", and then "$group" to reassemble the document. And then "$merge".

db.Offers.aggregate([
  { "$unwind": "$items" },
  {
    "$lookup": {
      "from": "Inventory",
      "localField": "items.id",
      "foreignField": "id",
      "as": "invItems"
    }
  },
  {
    "$set": {
      "items": {
        "$mergeObjects": [
          "$items",
          {
            "$setField": {
              "field": "itemName",
              "input": "$items",
              "value": { "$first": "$invItems.longName" }
            }
          }
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "items": { "$push": "$items" },
      "name": { "$first": "$name" }
    }
  },
  { "$merge": "Offers" }
])

Try it on mongoplayground.net.