MongoDB Aggregation - Group by two fields-CodePudding

I've already looked into possible duplicates but I could not find one. I have a MongoDB collection structure like this:

[
  {eui: 'abcd', date: '2023/01/23, 9:36 AM', temperature: 4.7},
  {eui: 'abcd', date: '2023/01/23, 8:31 AM', temperature: 14.3},
  {eui: 'abcd', date: '2023/01/22, 5:36 PM', temperature: 6.2},
  {eui: 'abcd', date: '2023/01/22, 3:36 PM', temperature: 5.0},
  {eui: 'fght', date: '2023/01/23, 9:36 AM', temperature: 4.9},
  {eui: 'fght', date: '2023/01/23, 8:31 AM', temperature: 1.7},
  {eui: 'fght', date: '2023/01/23, 5:36 AM', temperature: 2.8}
]

I need to group the documents by eui and by day (therefore aggregating each hour on the same day). Moreover, for each day the document must contain the max temperature, the min temperature, and the average temperature of the day.

The desired output is:

[
  {eui: 'abcd', date: '2023/01/23', min_t: 4.7, max_t:14.3 , avg_t: 9.5},
  {eui: 'abcd', date: '2023/01/22', min_t: 5.0, max_t: 6.2, avg_t: 5.6},
  {eui: 'fght', date: '2023/01/23', min_t: 1.7, max_t: 4.9, avg_t: 3.1},
]

and so on. So I want a document for each day/eui pair.

I have tried

db.temperatures.aggregate([
  {$match: {eui: "C0EE400001030C5F"}},
  {"$group": {
    "_id": { '$dateToString': { 'format': "%Y-%m-%d", 'date': "$date", }},
    "avg_temperature": {"$avg": "$temperature" },
    "min_temperature": {"$min": "$temperature" },
    "max_temperature": {"$max": "$temperature" },
    }
  },
  {"$sort": { "_id":-1 }}
])

and I successfully group by day, but I am matching a specific eui, and I cannot manage to group by eui.

PS I am using PyMongo, therefore I would love to obtain something that works in the python wrapper.

Thanks.

CodePudding user response：

You can group by date and eui.

db.collection.aggregate([
  {
    $match: {
      eui: "C0EE400001030C5F"
    }
  },
  {
    "$group": {
      "_id": {
        "date": {
          "$dateToString": {
            "format": "%Y-%m-%d",
            "date": "$date",
            
          }
        },
        eui: "$eui"
      },
      "avg_temperature": {
        "$avg": "$temperature"
      },
      "min_temperature": {
        "$min": "$temperature"
      },
      "max_temperature": {
        "$max": "$temperature"
      },
      
    }
  },
  {
    $project: {
      _id: 0,
      date: "$_id.date",
      eui: "$_id.eui",
      avg_temperature: 1,
      min_temperature: 1,
      max_temperature: 1
    }
  },
  {
    "$sort": {
      "eui": 1,
      "date": -1
    }
  }
])

Demo @ Mongo Playground