For example, I have something in my database like in customers collection.

{
  Max: {
    shoping_list: { 
      food: { Pizza: 2, Ramen: 1, Sushi: 5 }
    }
  },
  John: {
    shoping_list: { 
      food: { Pizza: 2, Ramen: 1, Burger: 1 }
    }
  }
}

In my backend, I want to get the sum of food

const request = await customers.aggregate([
   {
     $group: {
       _id: null,
       Pizza: {
         $sum: '$shoping_list.food.Pizza',
   },

Is there a way how to update or get the sum automatically without manually writing every food from the shopping_list?

CodePudding user response：

The design of the document may lead the query looks complex but still achievable.

1. $replaceRoot - Replace the input document with a new document.

   1.1. $reduce - Iterate the array and transform it into a new form (array).

   1.2. input - Transform key-value pair of current document $$ROOT to an array of objects such as: [{ k: "", v: "" }]

   1.3. initialValue - Initialize the value with an empty array. And this will result in the output in the array.

   1.4. in

   1.4.1. $concatArrays - Combine aggregate array result ($$value) with 1.4.2.

   1.4.2. With the $cond operator to filter out the document with { k: "_id" }, and we transform the current iterate object's v shoping_list.food to the array via $objectToArray.

2. $unwind - Deconstruct the foods array into multiple documents.

3. $group - Group by foods.k and perform sum for foods.v.

db.collection.aggregate([
  {
    $replaceRoot: {
      newRoot: {
        foods: {
          $reduce: {
            input: {
              $objectToArray: "$$ROOT"
            },
            initialValue: [],
            in: {
              $concatArrays: [
                "$$value",
                {
                  $cond: {
                    if: {
                      $ne: [
                        "$$this.k",
                        "_id"
                      ]
                    },
                    then: {
                      $objectToArray: "$$this.v.shoping_list.food"
                    },
                    else: []
                  }
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$foods"
  },
  {
    $group: {
      _id: "$foods.k",
      sum: {
        $sum: "$foods.v"
      }
    }
  }
])

Demo @ Mongo Playground