Group documents with MongoDB-CodePudding

I come from the world of relational databases and am having difficulty with a MongoDB query.

In my database I have telemetry documents with minute-by-minute records for each monitored system.

I'm tending to do a query that groups records by hour.

Below is a demonstration of the structure of my documents:

{
  _id: ObjectId("61847b83618fc8a6fb368ab7"),
  system_code: 22,
  date: ISODate("2021-08-01T00:00:01.000Z"),
  value1: 22.2372973251,
  value2: 20
}

Here's the structure of the query I'm trying to run:

db.telemetry.aggregate([
    {
        $match: {
            $and: [
                { system_code: 22 },
                { date: { $gte: ISODate("2021-08-01 00:00:00") } },
                { date: { $lte: ISODate("2021-08-01 02:00:00") } }
            ]
        }
    },
    {
        $addFields: {
            italianDate: {
                $dateToString: {
                    format: "%d/%m/%Y %H:00:00",
                    date: "$date"
                }
            }
        }
    },
    {
        $sort: {
            italianDate: 1
        }
    },
    {
        $group: {
            _id: {
                year: { $year: "$date" },
                month: { $month: "$date" },
                day: { $dayOfMonth: "$date" },
                hour: { $hour: "$date" }
            }
        }
    }
]);

The SQL query with PostgreSQL is exactly this:

SELECT TO_CHAR(t.date, 'YYYY-MM-DD HH24:00:00') AS italianDate, AVG(value1), AVG(value2) FROM telemetry t WHERE t.system_code = 22 AND t.date BETWEEN '2021-08-01 00:00:00' AND '2021-08-01 02:00:00' GROUP BY italianDate ORDER BY italianDate ASC

Thank you so much if you can help me.

CodePudding user response：

You are actually pretty close. You just need to put the $avg inside your $group stage. Another thing is you need to pay attention to the date formats. Refer to this official MongoDB document for the formats.

db.telemetry.aggregate([
  {
    $match: {
      system_code: 22,
      date: {
        $gte: ISODate("2021-08-01T00:00:00Z"),
        $lte: ISODate("2021-08-01T02:00:00Z")
      }
    }
  },
  {
    $addFields: {
      italianDate: {
        $dateToString: {
          format: "%Y-%m-%d %H:00:00",
          date: "$date"
        }
      }
    }
  },
  {
    $group: {
      _id: "$italianDate",
      avgValue1: {
        $avg: "$value1"
      },
      avgValue2: {
        $avg: "$value2"
      }
    }
  },
  {
    $sort: {
      _id: -1
    }
  }
])

Here is the Mongo playground for your reference.