I'm not used to mongoDB yet.

I would like to make them as below results using sample data. I want to find the sum of each field by day.

Even if I searched through several documents, I couldn't find the answer.

sample data

[
  {
    _id: 1,
    parentId: 1,
    'stats': {
      'type': {
        'a': {
          'n:1': 2,
          'n:2': 2
        },
        'b': {
          'n:1': 2,
          'n:2': 1,
          'n:3': 1
        },
        'c': {
          'n:5': 4
        }
      }
    },
    time: ISODate('2021-10-12T05:00:00Z')
  },
  {
    _id: 2,
    parentId: 1,
    'stats': {
      'type': {
        'a': {
          'n:1': 1,
        },
        'b': {
          'n:1': 2,
          'n:2': 3,
          'n:3': 4
        },
        'c': {
          'n:4': 2
        }
      }
    },
    time: ISODate('2021-10-12T06:00:00Z')
  },
  {
    _id: 3,
    parentId: 2,
    'stats': {
      'type': {
        'a': {
          'n:1': 3,
        },
        'b': {
          'n:2': 5,
          'n:3': 7
        },
        'c': {
          'n:1': 1,
          'n:5': 2
        }
      }
    },
    time: ISODate('2021-10-13T05:00:00Z')
  }
]

result

[
  { parentId: 1, 'n:1':7, 'n:2':6, 'n:3':5, 'n:4':2, 'n:5':4, year: '2021', month: '10', day:'12'},
  { parentId: 2, 'n:1':4, 'n:2':5, 'n:3':7, 'n:5':2, year: '2021', month: '10', day:'13'},
]

It should be grouped by parentId, field, year, month, day. Multiple field counts make me hard.

Please help me.

CodePudding user response：

You can do this in several different ways as the main need here is to restructure the data so we can properly $group it, here is what I feel is the most simple way to do it using various operators.

I will mention that if you're using version 5 the syntax can be simplified as they added the new $getField operator which allows you to access specific keys on an object. However as this version is still very new I choose to not use this for my solution:

db.collection.aggregate([
  {
    $addFields: {
      values: {
        $reduce: {
          input: {
            $map: {
              input: {
                "$objectToArray": "$stats.type"
              },
              as: "typeObj",
              in: {
                "$objectToArray": "$$typeObj.v"
              },
              
            }
          },
          initialValue: [],
          in: {
            "$concatArrays": [
              "$$this",
              "$$value"
            ]
          }
        }
      }
    }
  },
  {
    $unwind: "$values"
  },
  {
    $group: {
      _id: {
        parentId: "$parentId",
        key: "$values.k"
      },
      value: {
        $sum: "$values.v"
      },
      time: {
        $first: "$time"
      }
    }
  },
  {
    $group: {
      _id: "$_id.parentId",
      values: {
        $push: {
          k: "$_id.key",
          v: "$value"
        }
      },
      time: {
        $first: "$time"
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        "$mergeObjects": [
          {
            parentId: "$_id"
          },
          {
            "$arrayToObject": "$values"
          },
          {
            year: {
              $year: "$time"
            }
          },
          {
            month: {
              $month: "$time"
            }
          },
          {
            day: {
              "$dayOfMonth": "$time"
            }
          },
          
        ]
      }
    }
  }
])

Mongo Playground

CodePudding user response：

My proposal is this one. Instead of time: { $first: "$time" }, I use { $dateTrunc: { date: "$time", unit: "day" } }, it requires MongoDB version 5.0.

db.collection.aggregate([
   { $set: { stats: { $objectToArray: "$stats.type" } } },
   { $set: { stats: { $map: { input: "$stats.v", in: { $objectToArray: "$$this" } } } } },
   {
      $set: {
         stats: {
            $reduce: {
               input: "$stats",
               initialValue: [],
               in: { $concatArrays: ["$$value", "$$this"] }
            }
         }
      }
   },
   { $unwind: "$stats" },
   {
      $group: {
         _id: {
            parentId: "$parentId",
            day: { $dateTrunc: { date: "$time", unit: "day" } },
            k: "$stats.k"
         }, v: { $sum: "$stats.v" }
      }
   },
   {
      $group: {
         _id: {
            parentId: "$_id.parentId",
            day: "$_id.day"
         },
         stats: { $push: { k: "$_id.k", v: "$v" } }
      }
   },
   { $set: { stats: { $arrayToObject: "$stats" } } },
   { $set: { "_id.day": { $dateToParts: { date: "$_id.day" } } } },
   { $replaceRoot: { newRoot: { $mergeObjects: ["$_id", "$stats"] } } },
   { $set: { year: "$day.year", month: "$day.month", day: "$day.day" } }
])

Part

   { $set: { stats: { $objectToArray: "$stats.type" } } },
   { $set: { stats: { $map: { input: "$stats.v", in: { $objectToArray: "$$this" } } } } },
   {
      $set: {
         stats: {
            $reduce: {
               input: "$stats",
               initialValue: [],
               in: { $concatArrays: ["$$value", "$$this"] }
            }
         }
      }
   },

is equal to @TomSlabbaert answer:

  {
    $addFields: {
      values: {
        $reduce: {
          input: {
            $map: {
              input: {
                "$objectToArray": "$stats.type"
              },
              as: "typeObj",
              in: {
                "$objectToArray": "$$typeObj.v"
              },
              
            }
          },
          initialValue: [],
          in: {
            "$concatArrays": [
              "$$this",
              "$$value"
            ]
          }
        }
      }
    }
  },

The rest are just cosmetic differences.

Mongo Playground

In case you are not running MongoDB 5.0 replace

day: { $dateTrunc: { date: "$time", unit: "day" } }

by

year: { $year: "$time" }, 
month: { $month: "$time" }, 
day: { $dayOfMonth: "$time" } 

and convert back to a Date with $dateFromParts()