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

MongoDB Aggregate Multiple Grouping Based on Time

I’m trying to show the top customers in the last 24 hours and last 12 hours using one DB Query.
We save the total orders of the customer in the db every one hour.
The raw data are something like the below, but i changed with the data for confidentiality:

[
  {
    "_id": ObjectId("64c8de6e10f1e5f6958033d7"),
    "address": "nowhere",
    "city": "NY",
    "name": "ABD",
    "count": NumberInt(19),
    "timestamp": ISODate("2023-08-01T05:30:42.956Z")
  },
  {
    "_id": ObjectId("64c8de6e10f1e5f6958033d6"),
    "address": "somewhere",
    "city": "NY",
    "name": "WBE",
    "count": NumberInt(4),
    "timestamp": ISODate("2023-08-01T05:29:02.956Z")
  },
  {
    "_id": ObjectId("64c8de6e10f1e5f6958033da"),
    "address": "somewhere",
    "city": "NY",
    "name": "WBE",
    "count": NumberInt(18),
    "timestamp": ISODate("2023-08-01T08:30:42.956Z")
  }
  ...
]

The result i was trying to go for was something like:

{
  last12Hours: [{ name: "WBE", city:"NY", count: "18" }, {name: "ABD", city:"NY", count: "12"}],
  last24Hours: [{ name: "WBE", city:"NY", count: "30" }, {name: "ABD", city:"NY", count: "20"}],
}

This is simple if i run two queries where i change the timestamp match to 12 hours before and 24 hours before:

db.collection.aggregate([
  {
    $match: {
      timestamp: {
        $gte: ISODate("2023-08-01T03:36:38.059Z")
      },
    },
  },
  {
    $group: {
      _id: "$name",
      count: {
        $sum: "$count"
      },
     city: { $first: "$city"}
    },
  },
  {
    $sort: {
      count: -1
    }
  },
  {
    $limit: 5,
    
  },
  {
    $project: {
      _id: 0,
      name: "$_id",
      city: "$city",
      count: 1,
    },
  },
])

I also tried something that has to do with intervals but that was a disaster of a query and didn’t work as expected.

I apprecaite hints, im not supposidly asking for anyone to just solve it. Thank you!

Tags:

2

Answers


  1. 0

    For your scenario, you need the $facet stage to execute multiple aggregation pipelines within a single stage and result in a single document.

    Unsure what is your last12HoursTimestamp and last24HoursTimestamp, your query should be as below:

    db.collection.aggregate([
  {
    "$facet": {
      "last12Hours": [
        {
          $match: {
            timestamp: {
              $gte: /* Last 12 hours timestamp */
            }
          }
        },
        {
          $group: {
            _id: "$name",
            count: {
              $sum: "$count"
            },
            city: {
              $first: "$city"
            }
          },
          
        },
        {
          $sort: {
            count: -1
          }
        },
        {
          $limit: 5
        },
        {
          $project: {
            _id: 0,
            name: "$_id",
            city: "$city",
            count: 1,
            
          }
        }
      ],
      "last24Hours": [
        {
          $match: {
            timestamp: {
              $gte: /* Last 24 hours timestamp */
            }
          }
        },
        {
          $group: {
            _id: "$name",
            count: {
              $sum: "$count"
            },
            city: {
              $first: "$city"
            }
          },
          
        },
        {
          $sort: {
            count: -1
          }
        },
        {
          $limit: 5
        },
        {
          $project: {
            _id: 0,
            name: "$_id",
            city: "$city",
            count: 1,
            
          }
        }
      ]
    }
  }
])

    Demo @ Mongo Playground

    Bonus: Besides passing the timestamp, you may use the MongoDB operator & function to calculate the timestamps for the last 12 & 24 hours as well.

    db.collection.aggregate([
  {
    $set: {
      last12HoursTimestamp: {
        "$dateSubtract": {
          "startDate": "$$NOW",
          "unit": "hour",
          "amount": 12
        }
      },
      last24HoursTimestamp: {
        "$dateSubtract": {
          "startDate": "$$NOW",
          "unit": "hour",
          "amount": 24
        }
      },
      
    }
  },
  {
    "$facet": {
      "last12Hours": [
        {
          $match: {
            $expr: {
              $gte: [
                "$timestamp",
                "$last12HoursTimestamp"
              ]
            }
          }
        },
        {
          $group: {
            _id: "$name",
            count: {
              $sum: "$count"
            },
            city: {
              $first: "$city"
            }
          },
          
        },
        {
          $sort: {
            count: -1
          }
        },
        {
          $limit: 5
        },
        {
          $project: {
            _id: 0,
            name: "$_id",
            city: "$city",
            count: 1,
            
          }
        }
      ],
      "last24Hours": [
        {
          $match: {
            $expr: {
              $gte: [
                "$timestamp",
                "$last24HoursTimestamp"
              ]
            }
          }
        },
        {
          $group: {
            _id: "$name",
            count: {
              $sum: "$count"
            },
            city: {
              $first: "$city"
            }
          },
          
        },
        {
          $sort: {
            count: -1
          }
        },
        {
          $limit: 5
        },
        {
          $project: {
            _id: 0,
            name: "$_id",
            city: "$city",
            count: 1
          }
        }
      ]
    }
  }
])
    Login or Signup to reply.
  2. 0

    Since $facet is not using indexes, you may want to use it as late as possible in your pipeline. Another option, following @YongShun, is to $match and $group before the $facet using $cond. This will allow you to use the index on these stages, and enter the $facet stage with less documents:

    db.collection.aggregate([
  {$match: {timestamp: {$gte: /* Last 24 hours timestamp */}}},
  {$group: {
      _id: "$name", 
      count24: {$sum: "$count"},
      count12: {$sum: {$cond: [
            {$gte: ["$timestamp",/* Last 12 hours timestamp */]},
            "$count",
            0
      ]}},
      city: {$first: "$city"}
  }},
  {$facet: {
      last24Hours: [
        {$sort: {count24: -1}},
        {$limit: 5},
        {$project: {_id: 0, name: "$_id", city: 1, count: "$count24"}}
      ],
      last12Hours: [
        {$sort: {count12: -1}},
        {$limit: 5},
        {$match: {count12: {$ne: 0}}},
        {$project: {_id: 0, name: "$_id", city: 1, count: "$count12"}}
      ]
  }}
])

    See how it works on the playground example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search