Mongo Aggregate $project variable as field name

Question

Mongo 4.2

Have a bunch of data for server instance and CPU usage.

{
    "_id" : "1",
    "instance" : "172.00.00.01",
    "client" : "A",
    "date" : ISODate("2020-12-06T22:47:00.163Z"),
    "app" : 0.0133317
}

{
    "_id" : "2",
    "instance" : "172.00.00.01",
    "client" : "A",
    "date" : ISODate("2020-12-06T22:47:03.163Z"),
    "app" : 0.0400000
}

{
    "_id" : "3",
    "instance" : "172.00.00.02",
    "client" : "B",
    "date" : ISODate("2020-12-06T22:47:00.163Z"),
    "app" : 0.0800000
}

I am wanting this as my result, data grouped per minute and then fields for each instance with MAX "app" value.

{
    "_id" : {
        "minute" : 47,
        "hour" : 22,
        "day" : 6,
        "month" : 12,
        "year" : 2020
    },
    "172.00.00.01": 0.0400000,
    "172.00.00.02": 0.0800000
}

So my aggregate needs to group to get minutes:

{
    "$group": {
      "_id": {
        "minute": {
          "$minute": {
            "date": "$date",
            "timezone": "America/Chicago"
          }
        },
        "hour": {
          "$hour": {
            "date": "$date",
            "timezone": "America/Chicago"
          }
        },
        "day": {
          "$dayOfMonth": {
            "date": "$date",
            "timezone": "America/Chicago"
          }
        },
        "month": {
          "$month": {
            "date": "$date",
            "timezone": "America/Chicago"
          }
        },
        "year": {
          "$year": {
            "date": "$date",
            "timezone": "America/Chicago"
          }
        },
        instance: '$instance',
        client: '$client'
      },
      app: {
          $max: '$app'
      }
    }
  }

That pipeline gives me this as result:

{
    "_id" : {
        "minute" : 47,
        "hour" : 22,
        "day" : 6,
        "month" : 12,
        "year" : 2020,
        "instance" : "172.00.00.01",
        "client" : "A"
    },
    "app" : 0.040000
},
{
    "_id" : {
        "minute" : 47,
        "hour" : 22,
        "day" : 6,
        "month" : 12,
        "year" : 2020,
        "instance" : "172.00.00.02",
        "client" : "B"
    },
    "app" : 0.080000
}

So to get to the desired output, I understand I need to group again without instance/client to combine the two, but how do I dynamically get the instance variable as field name? These do not work!

{
    "$addFields": {
      "[$instance]": "$app"
    }
},

{
    "$addFields": {
      "[$$instance]": "$app"
    }
},

{
    "$addFields": {
      "$instance": "$app"
    }
},

{
    "$addFields": {
      "$$instance": "$app"
    }
},

user_7975771 · Answer

With your code, additionally

{
    $group: {
      _id: {
        day: "$_id.day",
        hour: "$_id.hour",
        minute: "$_id.minute",
        month: "$_id.month",
        "year": "$_id.year"
      },
      data: {
        $push: { k: "$_id.instance", v: "$app" }
      }
    }
  },
  {
    $addFields: {
      data: { "$arrayToObject": "$data" }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [ "$data", "$$ROOT" ]
      }
    }
  },
  {
    $project: {
      data: 0
    }
  }

Working Mongo playground

Mongo Aggregate $project variable as field name

1 Answers