How to do double group and push with two fields in MongoDB?-CodePudding

I have data like this:

[
  {
    "channel": "abc",
    "date": "2019-01-01",
    "revenue": 100,
    "quantity": 100,
  },
  {
    "channel": "xyz",
    "date": "2019-02-10",
    "revenue": 100,
    "quantity": 100,
  },
  {
    "channel": "def",
    "date": "2020-01-01",
    "revenue": 100,
    "quantity": 100,
  },
  {
    "channel": "abc",
    "date": "2021-06-01",
    "revenue": 100,
    "quantity": 100,
  },
  {
    "channel": "abc",
    "date": "2021-06-12",
    "revenue": 100,
    "quantity": 100,
  }
]

I want to group by channel and push data and again group by date (in month and year only) and push data and add a field after these pushes. The dates are all Date objects, not Strings. The avg_revenue is tot_revenue divided by tot_quantity.

[
  {
    "channel": "abc",
    "dates": [
      {
        "date": "2019-01",
        "totals": {
          "tot_revenue": 100,
          "tot_quantity": 100,
          "avg_revenue": 1,
        }
      },
      {
        "date": "2019-01",
        "totals": {
          "tot_revenue": 200,
          "tot_quantity": 200,
          "avg_revenue": 1,
        }
      }
    ]
  },
  {
    "channel": "def",
    "dates": [
      {
        "date": "2020-01",
        "totals": {
          "tot_revenue": 100,
          "tot_quantity": 100,
          "avg_revenue": 1,
        }
      }
    ]
  },
  {
    "channel": "xyz",
    "dates": [
      {
        "date": "2019-02",
        "totals": {
          "tot_revenue": 100,
          "tot_quantity": 100,
          "avg_revenue": 1,
        }
      }
    ]
  },
]

My attempt:

db.collection.aggregate([
  {
    "$set": {
      "date": {
        "$dateFromString": {
          "dateString": "$date",
          "format": "%Y-%m-%d"
        }
      }
    }
  },
  {
    $group: {
      _id: {
        channel: "$channel",
        month: {
          $month: "$date"
        },
        year: {
          $year: "$date"
        }
      },
      report_dates: {
        $push: {
          report_date: "$date",
          revenue: "$revenue",
          quantity: "$quantity",
          
        }
      },
      
    }
  },
  {
    $group: {
      _id: {
        month: "$month",
        year: "$year",
        
      },
      values: {
        $push: {
          revenue: "$revenue",
          quantity: "$quantity",
          
        }
      },
      
    }
  }
])

CodePudding user response：

You need to create an aggregation pipeline that consists of two $group steps, the first to group all the documents by the channel and date fields whilst accumulating the tot_revenue and tot_quantity aggregates. The other $group stage will compute the dates list with the totals.

The following pipeline should give the desired output:

db.collection.aggregate([
    { '$group': {
       '_id': { 
           'channel': '$channel', 
           'date': { 
               '$dateToString': { 
                  'format': "%Y-%m", 'date': {
                     "$dateFromString": {
                     "dateString": "$date",
                     "format": "%Y-%m-%d"
                  }
                }
              } 
           } 
        },
      'tot_revenue': { '$sum': '$revenue' },
      'tot_quantity': { '$sum': '$quantity' },   
    } },
    { '$group': {
       '_id': '$_id.channel',
       'dates': { 
          '$push': {
              'date': '$_id.date',
              'totals': {
                  'tot_revenue': '$tot_revenue',
                  'tot_quantity': '$tot_quantity',
                  'avg_revenue': { '$divide': ['$tot_revenue','$tot_quantity'] }
              }
          }
       }  
    } }      
])