Mongodb: group by (aggregate) two fields-CodePudding

I've already looked into possible duplicates but I could not find one. I have a mongoDB collection structured 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 in a 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 succesfully 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 sometinhg 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