CODEWITHSUNDEEP
mongodbaggregation-framework
Muhaimin
Muhaimin

Reputation: 1643

Aggregate to create dynamic key from value

I have an aggregation expression as the following:

db.Booking.aggregate([
      { $match: { created_at: {$exists:true} } },
      { $sort : { created_at : -1 } },
      { $group: {
          _id: {
            year : { $year : "$created_at" },
            month : { $month : "$created_at" },
            accepted: '$accepted'
          },
          total: {
            $sum: '$total'
          }
        } 
      }
    ])

Which generates results like this:

[
  {"_id":{"year":2017,"month":4,"accepted":"expired"},"total":0},
  {"_id":{"year":2017,"month":4,"accepted":"Expired"},"total":25},
  {"_id":{"year":2017,"month":4,"accepted":"Pending"},"total":390},
  {"_id":{"year":2017,"month":5,"accepted":"Pending"},"total":1400}
]

And how do I get a result like this:

[
  {"_id":{"year":2017,"month":4},"expired":0},
  {"_id":{"year":2017,"month":4},"Expired":25},
  {"_id":{"year":2017,"month":4},"Pending":390},
  {"_id":{"year":2017,"month":5},"Pending":1400}
]

Do I have to modify using regular javascript instead of relying Mongodb to do the job

Upvotes: 4

Views: 4226

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

You really "should" process the results to name these keys in your receiving code rather than relying on the database to do this. Whilst possible with the most recent current release it does require some wrangling.

This does require MongoDB 3.4 at least:

db.Booking.aggregate([
  { "$match": { "created_at": { "$exists": true } } },
  { "$sort": { "created_at": -1 } },
  { "$group": {
    "_id": {
      "year": { "$year": "$created_at" },
      "month": { "$month": "$created_at" },
      "accepted": {
        "$concat": [
          { "$toUpper": { "$substrCP": [ "$accepted", 0, 1 ] } },
          { "$toLower": { "$substrCP": [ "$accepted", 1, { "$strLenCP": "$accepted" } ] } }
        ]
      }
    },
    "total": { "$sum": "$total" }
  }},
  { "$replaceRoot": {
    "newRoot": { 
      "$arrayToObject": {
        "$concatArrays": [
          [
            { "k": "_id", "v": { "year": "$_id.year", "month": "$_id.month" } },
            { "k": "$_id.accepted", "v": "$total" }
          ]
        ]
      }
    }
  }}
])

Most of this is handled by $arrayToObject and the $replaceRoot pipeline stage. It may be noted that $concatArrays usage here seems necessary since $arrayToObject is complaining if simply supplied the array literal as an argument. So instead we use an operator which returns an array from it's own given expression, and $concatArrays seems to be the most brief notation.

This appears to be a bug ( to be confirmed ), but may well be just taken as intended usage since with most aggregation operators the array [] argument is usually intended for multiple arguments.

Also you appear to have a problem in your general $group statement since the "accepted" values differ in case and will be treated differently by MongoDB. You can correct this with usage of operators like $toLower and $toUpper. Here I use some modern operators to make a consistent first letter as uppercase and the rest lowercase, but the basic case functions are available in all versions supporting .aggregate().

Of course, post processing is very simple and can be done in any version. For JavaScript from the MongoDB shell it is as simple as follows:

db.Booking.aggregate([
  { "$match": { "created_at": { "$exists": true } } },
  { "$sort": { "created_at": -1 } },
  { "$group": {
    "_id": {
      "year": { "$year": "$created_at" },
      "month": { "$month": "$created_at" },
      "accepted": {
        "$concat": [
          { "$toUpper": { "$substrCP": [ "$accepted", 0, 1 ] } },
          { "$toLower": { "$substrCP": [ "$accepted", 1, { "$strLenCP": "$accepted" } ] } }
        ]
      }
    },
    "total": { "$sum": "$total" }
  }}
]).forEach(doc => {
  doc[doc._id.accepted] = doc.total;
  delete doc._id.accepted;
  delete doc.total;
  printjson(doc)
})

Both produce the same thing:

{ "_id" : { "year" : 2017, "month" : 4 }, "Expired" : 25 }
{ "_id" : { "year" : 2017, "month" : 4 }, "Pending" : 390 }
{ "_id" : { "year" : 2017, "month" : 5 }, "Pending" : 1400 }

If you in fact wanted "both" the "Pending" and "Expired" keys within the grouped results, then that is much simpler:

db.Booking.aggregate([
  { "$match": { "created_at": { "$exists": true } } },
  { "$group": {
    "_id": { 
      "year": { "$year": "$created_at" },
      "month": { "$month": "$created_at" }
    },
    "Expired": {
      "$sum": {
        "$cond": [
          { "$eq": [ { "$toLower": "$accepted" }, "expired" ] },
          "$total",
          0  
        ]
      }
    },
    "Pending": {
      "$sum": {
        "$cond": [
          { "$eq": [ { "$toLower": "$accepted" }, "pending" ] },
          "$total",
          0  
        ]
      }
    }
  }},
  { "$sort": { "_id": 1 } }
])

Which returns:

{ "_id" : { "year" : 2017, "month" : 4 }, "Expired" : 25, "Pending" : 390 }
{ "_id" : { "year" : 2017, "month" : 5 }, "Expired" : 0, "Pending" : 1400 }

Since there is a "fixed" format for the keys in the document any they do not vary, then this is a simple layout and we just use $cond to decide which values to accumulate under each key with $sum.

Upvotes: 2

Related Questions