I have data like this:

[
  {
    "channel": "abc",
    "date": "2019-01-01",
    "revenue": 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-08-10",
    "revenue": 100,
    "quantity": 100,
  },
  {
    "channel": "abc",
    "date": "2020-03-23",
    "revenue": 100,
    "quantity": 100,
  },
  {
    "channel": "abc",
    "date": "2021-08-12",
    "revenue": 100,
    "quantity": 100,
  }
]

I want to group by the year and channel and have separate revenues and quantities for the current and previous year only.

[
  {
    "channel": "abc",
    "current_year_revenue": 300,
    "prev_year_revenue": 100,
    "current_year_quantity": 300,
    "prev_year_quantity": 100,
  },
  {
    "channel": "def",
    "current_year_revenue": 100,
    "prev_year_revenue": 100,
    "current_year_quantity": 0,
    "prev_year_quantity": 0,
  }
]

CodePudding user response：

Its better to change your schema, and save dates as dates, not as strings. If you change your schema you can remove the $dateFromString part of the bellow query.

Query

• take the current-year,and the previous using the $$NOW system variable and $year operator
• take the year of the date
• keep only dates, of current or previous year
• group by channel, and sum with cond, in 4 accumulators

*Query is big but its the same code 4x times, accumulators are almost the same code.

Test code here

db.collection.aggregate([
  {
    "$set": {
      "date-year": {
        "$year": {
          "$dateFromString": {
            "dateString": "$date",
            "format": "%Y-%m-%d"
          }
        }
      },
      "cur-year": {
        "$year": "$$NOW"
      },
      "prv-year": {
        "$subtract": [
          {
            "$year": "$$NOW"
          },
          1
        ]
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$in": [
          "$date-year",
          [
            "$cur-year",
            "$prv-year"
          ]
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$channel",
      "cur_year_quantity": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$date-year",
                "$cur-year"
              ]
            },
            "$quantity",
            0
          ]
        }
      },
      "cur_year_revenue": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$date-year",
                "$cur-year"
              ]
            },
            "$revenue",
            0
          ]
        }
      },
      "prev_year_quantity": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$date-year",
                "$prv-year"
              ]
            },
            "$quantity",
            0
          ]
        }
      },
      "prev_year_revenue": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$date-year",
                "$prv-year"
              ]
            },
            "$revenue",
            0
          ]
        }
      }
    }
  },
  {
    "$set": {
      "channel": "$_id"
    }
  },
  {
    "$project": {
      "_id": 0
    }
  }
])