mongodb: assign value dynamically in a new field using parameter

user_14079866 0
Viewed 66

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
        }
    ]
}
{
....
}
mongodb aggregate
3 Answers

This will require quite a lot of structure manipulation as Mongo is really bad at dynamically accessing objects keys. You'll have to iterate every location object, transform it using $objectToArray then filtering out the relevant value like so:

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

MongoPlayground

user_107703700

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

user_89871280

A solution without using map reduce:

db.collection.aggregate([
                    {$unwind: "$locations"}, 
                    {$project: {
                                _id: 1,
                                customer: 1,
                                parameter: 1,
                                locations: 1,
                                locationsKV: {$objectToArray: "$locations"}
                                }
                    }, 
                    {$unwind: "$locationsKV"}, 
                    {$project: { 
                                _id: 1,
                                customer: 1,
                                parameter: 1,
                                locations: 1,
                                locationsKV: 1,
                                category: {
                                    $cond:[{$eq: ["$parameter", "$locationsKV.k"]}, "$locationsKV.v", 0]},
                                agg: {$cond: [{$eq: ["$parameter", "$locationsKV.k"]}, true, false]}
                                }
                     }, 
                     {$match: {agg: true}}, 
                     {$addFields: {"locations.category": "$category"}}, 
                     {$group: {
                             _id: "$_id",
                             customer: {$first: "$customer"},
                             parameter: {$first: "$parameter"},
                             locations: {$push: "$locations"}
                             }
                     }, 
                     {$project:{ _id: 0}} 
                     ])
user_140798660
Related