CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
bc110402922
bc110402922

Reputation: 457

how to label the field in MongoDB and make a sum with respect to date

Need to format the date and make a sum per day but sometimes a or b values are not available. in the end, get one document with respect to date and sum. I'm using MongoDB 4.2.

Data Structure:
{
  "data": {
    "11-10-2001": {
      "a": 17.281150000000001,
      "b": 11.864060000000006
    },
    "13-10-2020": {
      "b": 2.7616699999999994
    },
    "12-10-2001": {
      "b": 4.0809599999999997
    },
    "09-10-2001": {
      "b": 4.1286300000000005
    },
    "17-10-2001": {
      "a": 15.140560000000123,
      "b": 5.017139999999998
    },
    "18-10-2001": {
      "b": 1.975189999999997,
      "a": 7.093789999999976
    }
  }
}

Expected Output one document that contains the day and sum:

{
  {
    day: 11-10-2001,
    sum : 29.145
  },
  {
    day: 13-10-201,
    sum : 2.7616699
  },
  {
    day: 12-10-2001,
    sum  : 4.0809599999999997
  },
  {
    day: 17-10-2001,
    sum  : 20.114
  },
  {
    day: 18-10-2001,
    sum  : 9.145
  }
}

Upvotes: 1

Views: 587

Answers (2)

turivishal
turivishal

Reputation: 36114

You can try,

  • $map to iterate loop of data object after converting to array using $objectToArray
  • add key day, and sum, $reduce to loop of number object after converting to array using $objectToArray, $add to sum the value of number
  • $unwind deconstruct data array
  • $replaceRoot to replace data object to root
db.collection.aggregate([
  {
    $addFields: {
      data: {
        $map: {
          input: { $objectToArray: "$data" },
          in: {
            day: "$$this.k",
            sum: {
              $reduce: {
                input: { $objectToArray: "$$this.v" },
                initialValue: 0,
                in: { $add: ["$$this.v", "$$value"] }
              }
            }
          }
        }
      }
    }
  },
  { $unwind: "$data" },
  { $replaceRoot: { newRoot: "$data" } }
])

Playground

Upvotes: 2

varman
varman

Reputation: 8894

You can do like following

db.collection.aggregate([
  {
    $project: { data: { "$objectToArray": "$data" } }
  },
  {
    $unwind: "$data"
  },
  {
    "$replaceRoot": { "newRoot": "$data" }
  },
  {
    $addFields: { v: { "$objectToArray": "$v" } }
  },
  {
    $addFields: {
      v: {
        $reduce: {
          input: "$v",
          initialValue: 0,
          in: {
            $add: [ "$$this.v", "$$value" ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      data: {
        $push: {
          day: "$k",
          sum: "$v"
        }
      }
    }
  }
])

Working Mongo playground

Upvotes: 1

Related Questions