Update array at specific index by other filed in MongoDB

Question

I have a collection, consist of name and data.

data is an array with 2 elements, each element is the object with code and qty.

{
    "_id" : ObjectId("605c666a15d2612ed0afedd2"),
    "name" : "Anna",
    "data" : [
        {
            "code" : "a",
            "qty" : 3
        },
        {
            "code" : "b",
            "qty" : 4
        }
    ]
},
{
    "_id" : ObjectId("605c666a15d2612ed0afedd3"),
    "name" : "James",
    "data" : [
        {
            "code" : "c",
            "qty" : 5
        },
        {
            "code" : "d",
            "qty" : 6
        }
    ]
}

I want to update the code of the first element to name of its document. The result I want is

{
    "_id" : ObjectId("605c666a15d2612ed0afedd2"),
    "name" : "Anna",
    "data" : [
        {
            "code" : "Anna",
            "qty" : 3
        },
        {
            "code" : "b",
            "qty" : 4
        }
    ]
},
{
    "_id" : ObjectId("605c666a15d2612ed0afedd3"),
    "name" : "James",
    "data" : [
        {
            "code" : "James",
            "qty" : 5
        },
        {
            "code" : "d",
            "qty" : 6
        }
    ]
}

I just google to find how to:

1. update array at a specific index (https://stackoverflow.com/a/34177929/11738185)

db.Collection.updateMany(
    { },
    {
        $set:{
            'data.0.code': '$name'
        }
    }
)

But the code of the first element in data array is a string '$name', not a value (Anna, James)

{
    "_id" : ObjectId("605c666a15d2612ed0afedd2"),
    "name" : "Anna",
    "data" : [
        {
            "code" : "$name",
            "qty" : 3
        },
        {
            "code" : "b",
            "qty" : 4
        }
    ]
},
{
    "_id" : ObjectId("605c666a15d2612ed0afedd3"),
    "name" : "James",
    "data" : [
        {
            "code" : "$name",
            "qty" : 5
        },
        {
            "code" : "d",
            "qty" : 6
        }
    ]
}

2. update a field by the value of another field. It takes me to use pipeline updating (https://stackoverflow.com/a/37280419/11738185): the second param of updateMany is array (pipeline)

db.Collection.updateMany(
    { },
    [{
        $set:{
            'data.0.code': '$name'
        }
    }]
)

and It adds field 0 to each element in data array

{
    "_id" : ObjectId("605c666a15d2612ed0afedd2"),
    "name" : "Anna",
    "data" : [
        {
            "0" : {
                "code" : "Anna"
            },
            "code" : "a",
            "qty" : 3
        },
        {
            "0" : {
                "code" : "Anna"
            },
            "code" : "b",
            "qty" : 4
        }
    ]
},
{
    "_id" : ObjectId("605c666a15d2612ed0afedd3"),
    "name" : "James",
    "data" : [
        {
            "0" : {
                "code" : "James"
            },
            "code" : "c",
            "qty" : 5
        },
        {
            "0" : {
                "code" : "James"
            },
            "code" : "d",
            "qty" : 6
        }
    ]
}

I can't find the solution for this case. Could anyone to help me? How can I update array at fixed index by other field. Thanks for reading!

Answer 1

1. update array at a specific index

You can't use internal fields as value of another fields, it will work only when you have external value to update like { $set: { "data.0.code": "Anna" } }.

2. update a field by the value of another field

Update with Aggregation pipeline can't allow to access data.0.code syntax.

---

You can try using $reduce in update with aggregation pipeline,

• $reduce to iterate loop of data array, set empty array in initialValue of reduce, Check condition if initialValue array size is zero then replace code with name and merge with current object using $mergeObjects, else return current object,
• $concatArrays to concat current object with initialValue array

db.collection.update({},
  [{
    $set: {
      data: {
        $reduce: {
          input: "$data",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              [
                {
                  $cond: [
                    { $eq: [{ $size: "$$value" }, 0] },
                    { $mergeObjects: ["$$this", { code: "$name" }] },
                    "$$this"
                  ]
                }
              ]
            ]
          }
        }
      }
    }
  }],
  { multi: true }
)

Playground