Mongodb Aggregation pipeline: different $match to show different results on the same value in the sa-CodePudding

Lets take as an example the following book collection :

{BookDate: "BOOKA-2010", Price: "1", BookName: "BOOKA"},
{BookDate: "BOOKA-2011", Price: "2", BookName: "BOOKA"},
{BookDate: "BOOKA-2012", Price: "3", BookName: "BOOKA"},
{BookDate: "BOOKA-2013", Price: "4", BookName: "BOOKA"},
{BookDate: "BOOKA-2014", Price: "5", BookName: "BOOKA"},
{BookDate: "BOOKB-2010", Price: "6", BookName: "BOOKB"},
{BookDate: "BOOKB-2011", Price: "7", BookName: "BOOKB"},
{BookDate: "BOOKB-2012", Price: "8", BookName: "BOOKB"},
{BookDate: "BOOKB-2013", Price: "9", BookName: "BOOKB"},
{BookDate: "BOOKB-2014", Price: "10",BookName: "BOOKB"}

I need to calculate the sum of the books prices in two different year ranges in the same result, the desired output will look something like the following:

{BookName: "BOOKA", PriceSum2010-2011: "3", PriceSum2013-2014: "9"},
{BookName: "BOOKB", PriceSum2010-2011: "13", PriceSum2013-2014: "19"}

the given ranges for the examples 2010-2011 and 2013-2014 without considering 2012.

The pipeline I have for now is :

[
    {
        $match: {
            BookDate: /201[0-1]/,
        },
    },
    {
        $group: {
            _id: '$BookName',
            'PriceSum2010-2011': {
                $sum: '$PRICE',
            },
        },
    },
]

This gives back half of what I need, it only returns the price sum of the 2010-2011 range, and if I try to add another field to the match, it overrides the previous one, any suggestions please?

Thanks!

CodePudding user response：

I recommend just doing the $sum with a $cond expression, this means we sum price when the condition is matched, otherwise we sum 0, like so:

db.collection.aggregate([
  {
    $group: {
      _id: "$BookName",
      "PriceSum2010-2011": {
        $sum: {
          $cond: [
            {
              $regexMatch: {
                input: "$BookDate",
                regex: "201[0-1]"
              }
            },
            {
              "$toInt": "$Price"
            },
            0
          ]
        }
      },
      "PriceSum2013-2014": {
        $sum: {
          $cond: [
            {
              $regexMatch: {
                input: "$BookDate",
                regex: "201[3-4]"
              }
            },
            {
              "$toInt": "$Price"
            },
            0
          ]
        }
      }
    }
  }
])

Mongo Playground