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

MongoDB group by date and select values

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 🙂

Tags:

3

Answers


  1. 0

    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!

    Login or Signup to reply.
  2. 0

    You can do it with Aggregation framework:

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

    Working example

    Login or Signup to reply.
  3. 0

    To increase performance, first it would be good to create index for "date" field in your collection. By doing that, group by date in aggregation pipeline will be much faster. You can do that by adding this in your file where you have defined Schema for your collection. "Schema" is name of schema for your collection.

    Schema.index({
  date: 1,
});

    Do not forget to rebuild indexes for specific collection. You can do that by typing this in mongo shell, where "collection" is name of your collection:

    db.collection.reIndex()

    And you need to add group stage in aggregation pipeline.

      {
    "$group" : {
      "_id" : "$date",
      "docs": { $push : "$$ROOT" },
    }
  }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search