MongoDB group by date and select values-CodePudding

I'm relatively new to MongoDB and node.js. Here is what I'm trying to achieve:

I have a collection that contains several thousand documents in MongoDB. The documents look like this:

    {
     "date": "2020-02-24",
     "iso_code": "USA",
     "country": "USA",
     "avg_temperature": "25F",
    },
    {
     "date": "2020-02-25",
     "iso_code": "USA",
     "country": "USA",
     "avg_temperature": "27F",
    },
    {
     "date": "2020-02-24",
     "iso_code": "CHN",
     "country": "China",
     "avg_temperature": "10C",
    }

I want to avoid running costly calculation in node.js, so I would like to receive the temperatures grouped by date and retrieve the actual values for avg_temperature (so no $min or $max calculation). I expect the result to look something like this:

    { 
        "date": "2020-02-24", 
        "avg_temperatures": {
            "USA": "25F", 
            "China": "10C"
        }
     }

My ultimate goal is to create a chart that visualizes the data over a timeline for multiple countries. My idea is to simply iterate through the contents of the result and then trough the temperatures and display the data.

If someone has done something similar before, feel free to suggest a better alternative :)

CodePudding user response：

apparently getting the result as you described will be a little complex. Instead, you can do it by making a simple aggregation pipeline like this

var pipeline = [
        {
            "$group" : {
                "_id" : "$date",
                "docs" : {
                    "$push" : {
                        "country" : "$country",
                        "temp" : "$avg_temperature"
                    }
                }
            }
        }
    ];

For the above pipeline you will get the output like

db.stack.aggregate(pipeline).pretty()
{
    "_id" : "2020-02-25",
    "docs" : [
        {
            "country" : "USA",
            "temp" : "27F"
        }
    ]
}
{
    "_id" : "2020-02-24",
    "docs" : [
        {
            "country" : "USA",
            "temp" : "25F"
        },
        {
            "country" : "China",
            "temp" : "10C"
        }
    ]
}

I hope this will solve your problem!

CodePudding user response：

You can do it with Aggregation framework:

db.collection.aggregate([
  {
    "$group": {
      "_id": "$date",
      "avg_temperatures": {
        "$mergeObjects": {
          "$arrayToObject": [
            [
              {
                k: "$country",
                v: "$avg_temperature"
              }
            ]
          ]
        }
      }
    }
  }
])

Working example