Question 115433 in Mongodb Question posted in
The official documentation can be found here.

Create unique field in MongoDB aggregate when grouping by two fields, and use for sorting

When aggregating a collection with the following pipeline stages

let ap = [{
          $group: {
            _id: {
              year: { $year: '$itemDate' },
              month: { $month: '$itemDate' },
            },
            count: { $sum: 1 },
          },
        }]

I receive something like this…

[
  { _id: { year: 2022, month: 9 }, count: 2 },
  { _id: { year: 2022, month: 4 }, count: 232 },
  { _id: { year: 2022, month: 3 }, count: 259 },
  { _id: { year: 2022, month: 8 }, count: 12 },
  { _id: { year: 2022, month: 1 }, count: 76 }
]

I would like to use an $addFields stage (or similar) such that my results would have a key and be sorted by year-month, like this

[
  { _id: { year: 2022, month: 1 }, count: 76, key: '2022-01'},
  { _id: { year: 2022, month: 3 }, count: 259, key: '2022-03'},
  { _id: { year: 2022, month: 4 }, count: 232, key: '2022-04'},
  { _id: { year: 2022, month: 8 }, count: 12, key: '2022-08'},
  { _id: { year: 2022, month: 9 }, count: 2, key: '2022-09'},
]

I have tried a template string, but it does not work. I also don’t know how this would be sortable either…

ap.push({
  $addFields: {
    key: `${'$_id.year'}-${'$_id.month'}`,
    key2: '$_id.year',
   },
});

Any help is greatly appreciated.

Tags:

3

Answers


  1. 0

    Use $concat to chain up the result from $toString

    db.collection.aggregate([
  {
    "$addFields": {
      "key": {
        "$concat": [
          {
            "$toString": "$_id.year"
          },
          "-",
          {
            "$toString": "$_id.month"
          }
        ]
      }
    }
  },
  {
    $sort: {
      key: 1
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    You can use a 3-step aggregation pipeline using $project, $group and $sort. It can look something like this:

    [
  {
    '$project': {
      '_id': true, 
      'itemDate': true, 
      'key': {
        '$concat': [
          {
            '$toString': {
              '$year': '$itemDate'
            }
          }, '-', {
            '$toString': {
              '$month': '$itemDate'
            }
          }
        ]
      }
    }
  }, {
    '$group': {
      '_id': '$key', 
      'count': {
        '$sum': 1
      }
    }
  }, {
    '$sort': {
      'key': -1
    }
  }
]
    Login or Signup to reply.
  3. 0

    Option 1:

    You don’t need a separate key to sort. Just sort on _id.year and _id.month.

    db.collection.aggregate([
  {
    $group: {
      _id: {
        year: {$year: "$itemDate"},
        month: {$month: "$itemDate"},
        
      },
      count: {$sum: 1}
    }
  },
  {
    $sort: {
      "_id.year": 1,"_id.month": 1
    }
  }
])

    Demo

    Option 2:

    Instead of having another stage, create the field in $group stage itself. Convert the date to string and pick the $first from the group.

    db.collection.aggregate([
  {
    $group: {
      _id: {
        year: {$year: "$itemDate"},
        month: {$month: "$itemDate"},
        
      },
      count: {$sum: 1},
      key: {
        $first: {
          $dateToString: {date: "$itemDate", format: "%Y-%m"}
        }
      }
    }
  },
  {
    $sort: {
      "key": 1
    }
  }
])

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search