Best Way to Group by Date with Mongoid

Best way to group by date with Mongoid

Here's an answer that uses the aggregation framework to group by date. I hope that you like it.

app/model/patient.rb

class Patient
  include Mongoid::Document
  field :name, type: String

  def self.group_by(field, format = 'day')
    key_op = [['year', '$year'], ['month', '$month'], ['day', '$dayOfMonth']]
    key_op = key_op.take(1 + key_op.find_index { |key, op| format == key })
    project_date_fields = Hash[*key_op.collect { |key, op| [key, {op => "$#{field}"}] }.flatten]
    group_id_fields = Hash[*key_op.collect { |key, op| [key, "$#{key}"] }.flatten]
    pipeline = [
        {"$project" => {"name" => 1, field => 1}.merge(project_date_fields)},
        {"$group" => {"_id" => group_id_fields, "count" => {"$sum" => 1}}},
        {"$sort" => {"count" => -1}}
    ]
    collection.aggregate(pipeline)
  end
end

test/unit/patient_test.rb

require 'test_helper'
require 'pp'

class PatientTest < ActiveSupport::TestCase
  def setup
    Patient.delete_all
  end

  test "group by date" do
    [
        {"name" => "John", "created_at" => Date.new(2012, 10, 10).mongoize},
        {"name" => "Jane", "created_at" => Date.new(2012, 10, 31).mongoize},
        {"name" => "Mary", "created_at" => Date.new(2012, 10, 31).mongoize},
        {"name" => "Mark", "created_at" => Date.new(2012, 12, 12).mongoize},
        {"name" => "Alex", "created_at" => Date.new(2013, 11, 10).mongoize},
        {"name" => "Andy", "created_at" => Date.new(2013, 10, 31).mongoize},
        {"name" => "Toni", "created_at" => Date.new(2013, 10, 31).mongoize},
        {"name" => "Cori", "created_at" => Date.new(2013, 11, 10).mongoize}
    ].each do |patient|
      Patient.create(patient)
    end
    puts "\nMongoid::VERSION:#{Mongoid::VERSION}\nMoped::VERSION:#{Moped::VERSION}"
    pp Patient.group_by('created_at', 'month')
  end
end

$ rake test

Run options:

# Running tests:

[1/1] PatientTest#test_group_by_date
Mongoid::VERSION:3.1.5
Moped::VERSION:1.5.1
[{"_id"=>{"year"=>2012, "month"=>10}, "count"=>3},
 {"_id"=>{"year"=>2013, "month"=>10}, "count"=>2},
 {"_id"=>{"year"=>2013, "month"=>11}, "count"=>2},
 {"_id"=>{"year"=>2012, "month"=>12}, "count"=>1}]
Finished tests in 0.042561s, 23.4957 tests/s, 0.0000 assertions/s.
1 tests, 0 assertions, 0 failures, 0 errors, 0 skips

Group by date in mongoDB while counting other fields

You can do like following, what have you done is excellent. After that,

1. In second grouping, We just get total value and value of each recipe.
2. $map is used to go through/modify each objects
3. $arrayToObject is used to covert the array what we have done via map (key : value pair) to object
4. $ifNull is used for, sometimes your data might not have "A" or "B" or "C". But you need the value should be 0 if there is no name as expected output.

Here is the code

[
  {
    "$project": {
      "createdAt": {
        "$dateToString": {
          "format": "%Y-%m-%d",
          "date": "$timestamp"
        }
      },
      recipe: 1,
      "progressivo": 1,
      "temperatura_fusione": 1
    }
  },
  {
    "$group": {
      "_id": {
        "createdAt": "$createdAt",
        "recipeName": "$recipe",
        
      },
      "products": {
        $sum: 1
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.createdAt",
      value: {
        $sum: "$products"
      },
      recipes: {
        $push: {
          name: "$_id.recipeName",
          val: "$products"
        }
      }
    }
  },
  {
    $project: {
      "content": {
        "$arrayToObject": {
          "$map": {
            "input": "$recipes",
            "as": "el",
            "in": {
              "k": "$$el.name",
              "v": "$$el.val"
            }
          }
        }
      },
      value: 1
    }
  },
  {
    $project: {
      _id: 1,
      value: 1,
      A: {
        $ifNull: [
          "$content.A",
          0
        ]
      },
      B: {
        $ifNull: [
          "$content.B",
          0
        ]
      },
      C: {
        $ifNull: [
          "$content.C",
          0
        ]
      }
    }
  }
]

Working Mongo playground

MongoDB: Group by date and other value

You can change your query,

• $group by floor_price field and get max date by $max
• $sort by date in ascending order

let history = await History.aggregate([
  {
    $match: {
      address: "123456",
      token_to_index: "1"
    }
  },
  {
    $group: {
      _id: "$stats.floor_price",
      date: { $max: "$date" },
      count: { $sum: 1 }
    }
  },
  { $sort: { "date": 1 } }
])

Playground

How to get and then group by date range -weekly- in MongoDB?

It seems you can use $week and $group. There is a simple solution without the group names:

db.collection.aggregate([
  {
    $match: {
      createdBy: mongoose.Types.ObjectId(req.user.userId),
      dateCreated: {
        $gte: moment().startOf('week').week(-6).toDate(),
        $lt: moment().startOf('week').toDate()
      },
    }
  },
  {
    "$addFields": {
      "week": {$week: "$dateCreated"}
    }
  },
  {
    $project: {data: "$$ROOT"}
  },
  {
    $group: {
      _id: "$data.week",
      week: {$first:"$data.week"},
      data: {$push: "$data"}
    }
  }
])

In order to get something similar to your expected results.
You can see how it works on the playground.

If it is important to name the groups 'weekOne', 'weekTwo', you can do it by adding a step, finding the week group name from a given list of group names:

db.collection.aggregate([
  {
    $match: {
      createdBy: mongoose.Types.ObjectId(req.user.userId),
      dateCreated: {
        $gte: moment().startOf('week').startOf('week').toDate(),
        $lt: moment().startOf('week').toDate()
      },
    }
  },
  {
    "$addFields": {
      "weekNum": {$week: "$dateCreated"},
      "currentWeek": {$week: ISODate("2022-04-07T21:24:36.000Z")},
    }
  },
  {
    $project: {
      data: "$$ROOT",
      weeksOrder: [
        "WeekOne",
        "WeekTwo",
        "WeekTree",
        "WeekFour",
        "WeekFive",
        "WeekSix"
      ],
      weekIndex: {$subtract: ["$currentWeek", "$weekNum"]
      }
    }
  },
  {
    $project: {
      data: 1,
      weekGroup: {"$arrayElemAt": ["$weeksOrder", "$weekIndex"]}
    }
  },
  {
    $group: {
      _id: "$weekGroup",
      week: {$first: "$weekGroup"},
      data: {$push: "$data"}
    }
  }
])

As you can see on the playground as well

I want to group by date, but how can I do it in mongodb?

$group

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        year: {
          $year: "$createdAt"
        },
        month: {
          $month: "$createdAt"
        },
        day: {
          $dayOfMonth: "$createdAt"
        }
      },
      "Sales": {
        "$sum": 1
      },
      "Own Sales Volume": {
        "$sum": {
          $cond: [
            {
              $and: [
                {
                  $isArray: "$details"
                },
                {
                  $gt: [
                    {
                      "$size": "$details"
                    },
                    0
                  ]
                }
              ]
            },
            1,
            0
          ]
        }
      }
    }
  }
])

mongoplayground

How to group by date with interval in Mongo

Your conditions are not fully clear, have a look at this outline:

        connection      disconnection
————————————|————————————————|——————————————> t
                                        

 start + end
  x     x   |                |             | NO
        x   |           x    |             | YES/NO?
            |     x     x    |             | YES
            |     x          |    x        | YES/NO?
        x   |                |    x        | YES/NO?
            |                |    x    x   | NO

One approach is to generate intervals of 15 Minutes and then filter on these intervals. Could be this one:

db.collection.aggregate([
   {
      $group: {
         _id: null,
         data: { $push: "$$ROOT" },
         // Determine total min. and max. time. Might be replaced by static values
         min: { $min: "$connection" },
         max: { $max: "$disconnection" }
      }
   },
   {
      $set: {
         // Round min/max time to 15 Minute interval
         min: { $dateTrunc: { date: "$min", unit: "minute", binSize: 15 } },
         max: { $dateTrunc: { date: "$max", unit: "minute", binSize: 15 } }
      }
   },
   {
      $set: {
         // Get number of 15-Minute intervals between min and max
         steps: {
            $dateDiff: {
               startDate: "$min",
               endDate: "$max",
               unit: "minute"
            }
         }
      }
   },
   {
      $set: {
         // Generate the 15-Minute intervals
         intervals: {
            $map: {
               input: { $range: [0, "$steps", 15] },
               as: "t",
               in: {
                  start: {
                     $dateAdd: {
                        startDate: "$min",
                        unit: "minute",
                        amount: "$$t"
                     }
                  },
                  end: {
                     $dateAdd: {
                        startDate: "$min",
                        unit: "minute",
                        amount: { $add: ["$$t", 15] }
                     }
                  }
               }
            }
         }
      }
   },
   // Transpose array to documents
   { $unwind: "$intervals" },
   // Just some cosmetic
   { $project: { data: 1, start: "$intervals.start", end: "$intervals.end" } },
   {
      $set: {
         data: {
            // Filter matching connections (based on outline above)
            $filter: {
               input: "$data",
               cond: { $and: [{ $lte: ["$$this.connection", "$start"] }, { $gte: ["$$this.disconnection", "$end"] }] }
            }
         }
      }
   },
   // Skip all 15-Minute intervals without any connection
   { $match: { data: { $ne: [] } } },
   // Count users and some cosmetic
   {
      $project: {
         start:1,
         end: 1,
         count: { $size: "$data" },
         user: { $setUnion: "$data.user" }
      }
   }
])

Another approach could be $bucket but would be difficult, because it works only with numeric values, i.e. you have to twist around Date values.

Just a tip: Create a helper function

function between(ts, start, end) {
   let ret = {};
   if (typeof ts == "string") {
      ret["$expr"] = { $and: [{ $lte: ["$" + start, "$" + ts] }, { $gte: ["$" + end, "$" + ts] }] };
   } else {
      ret[start] = { $lte: ts };
      ret[end] = { $gte: ts };
   }
   return ret;
}

It corresponds to SQL BETWEEN Operator, i.e. WHERE ts BETWEEN start AND end

Then you can compose conditions like this

{ $match: between("start", "connection", "disconnection") }
{ $match: between(new Date(), "connection", "disconnection") }

which makes it much easier to write your query.

---

Related Topics