mongodb: assign value dynamically in a new field using parameter

Question

Assume the following collection received during an aggregation phase:

{
    customer: "WN",
    parameter: "category_a",
    locations: [ 
        {
            city: "Elkana", 
            category_a: 11904.0, 
            category_b: 74.0,
            category_c: 657.0,
        }, 
        {
            city: "Haifa",
            category_a: 20.0,
            category_b: 841.0,
            category_c: 0,
        }
    ]
}
{
    customer: "QKD",
    parameter: "category_b",
    locations: [ 
        {
            city: "Tel Aviv", 
            category_a: 0, 
            category_b: 22.0,
            category_c: 612.0,
        }
}
{
....
}

The value in the parameter field refer to the field name in the nested documents.

In the next phase I want to create new field in each nested document (I will call it category) and to assign one of the values in the nested document field (the value of category_a or the value of category_b or the value of category_c). The choice of which field the value will be taken from will be made using the value in the parameter field.

The results can be look like the following:

{
    customer: "WN",
    parameter: "category_a",
    locations: [ 
        {
            city: "Elkana", 
            category_a: 11904.0, 
            category_b: 74.0,
            category_c: 657.0,
            category: 11904.0
        }, 
        {
            city: "Haifa",
            category_a: 20.0,
            category_b: 841.0,
            category_c: 0,
            category: 20.0
        }
    ]
}
{
    customer: "QKD",
    parameter: "category_b",
    locations: [ 
        {
            city: "Tel Aviv", 
            category_a: 0, 
            category_b: 22.0,
            category_c: 612.0,
            category: 22.0
        }
    ]
}
{
....
}

Answer 1

You can try with $map and $reduce,

• $map to locations array
• $reduce location's object, input to $objectToArray it will convert object to array and $cond will check if parameter and k will match then merge objects using $mergeObjects.

db.collection.aggregate([
  {
    $addFields: {
      locations: {
        $map: {
          input: "$locations",
          as: "l",
          in: {
            $reduce: {
              input: { $objectToArray: "$$l" },
              initialValue: "$$l",
              in: {
                $cond: [
                  { $eq: ["$$this.k", "$parameter"] },
                  { $mergeObjects: ["$$value", { category: "$$this.v" }] },
                  "$$value"
                ]
              }
            }
          }
        }
      }
    }
  }
])

Playground