Note: Mongo version 3.6.2.
I have a document that looks like this:
const Ticket = new mongoose.Schema({ event_id: [ { required: false, type: mongoose.Schema.Types.ObjectId, ref: 'Event' } ], ticket_type: String, createdAt: String, }, { collection: 'tickets' }); I want to do a mongodb groupBy on ticket_type and createdAt where an event_id = X. So the output should like this:
[{ ticket_type: 'VIP', date: 2011-11-11, count: 12}, {..}] The hard part is that createdAt is stored as timemillis in a string, ex:
{ _id : ObjectId(123), ticket_type: 'VIP', createdAt: '1233434', event_id: [ObjectId(345)] } The answer should look something like this:
Ticket.find({ event_id: req.params.event_id }, function(err, tickCount) { if (err) { console.log(err); } else { res.json(tickCount); } }); Any help is greatly appreciated. Thanks.
This is what I came up with:
Ticket.aggregate([ { $match: { event_id: ObjectId(req.body.event_id)} }, { $addFields: { "createdAt": { $reduce: { "input": { $map: { // split string into char array so we can loop over individual characters "input": { $range: [ 0, { $strLenCP: "$createdAt" } ] // using an array of all numbers from 0 to the length of the string }, "in":{ $substrCP: [ "$createdAt", "$$this", 1 ] // return the nth character as the mapped value for the current index } } }, "initialValue": { // initialize the parser with a 0 value "n": 0, // the current number "sign": 1, // used for positive/negative numbers "div": null, // used for shifting on the right side of the decimal separator "." "mult": 10 // used for shifting on the left side of the decimal separator "." }, // start with a zero "in": { $let: { "vars": { "n": { $switch: { // char-to-number mapping branches: [ { "case": { $eq: [ "$$this", "1" ] }, "then": 1 }, { "case": { $eq: [ "$$this", "2" ] }, "then": 2 }, { "case": { $eq: [ "$$this", "3" ] }, "then": 3 }, { "case": { $eq: [ "$$this", "4" ] }, "then": 4 }, { "case": { $eq: [ "$$this", "5" ] }, "then": 5 }, { "case": { $eq: [ "$$this", "6" ] }, "then": 6 }, { "case": { $eq: [ "$$this", "7" ] }, "then": 7 }, { "case": { $eq: [ "$$this", "8" ] }, "then": 8 }, { "case": { $eq: [ "$$this", "9" ] }, "then": 9 }, { "case": { $eq: [ "$$this", "0" ] }, "then": 0 }, { "case": { $and: [ { $eq: [ "$$this", "-" ] }, { $eq: [ "$$value.n", 0 ] } ] }, "then": "-" }, // we allow a minus sign at the start { "case": { $eq: [ "$$this", "." ] }, "then": "." } ], default: null // marker to skip the current character } } }, "in": { $switch: { "branches": [ { "case": { $eq: [ "$$n", "-" ] }, "then": { // handle negative numbers "sign": -1, // set sign to -1, the rest stays untouched "n": "$$value.n", "div": "$$value.div", "mult": "$$value.mult", }, }, { "case": { $eq: [ "$$n", null ] }, // null is the "ignore this character" marker "then": "$$value" // no change to current value }, { "case": { $eq: [ "$$n", "." ] }, "then": { // handle decimals "n": "$$value.n", "sign": "$$value.sign", "div": 10, // from the decimal separator "." onwards, we start dividing new numbers by some divisor which starts at 10 initially "mult": 1, // and we stop multiplying the current value by ten }, }, ], "default": { "n": { $add: [ { $multiply: [ "$$value.n", "$$value.mult" ] }, // multiply the already parsed number by 10 because we're moving one step to the right or by one once we're hitting the decimals section { $divide: [ "$$n", { $ifNull: [ "$$value.div", 1 ] } ] } // add the respective numerical value of what we look at currently, potentially divided by a divisor ] }, "sign": "$$value.sign", "div": { $multiply: [ "$$value.div" , 10 ] }, "mult": "$$value.mult" } } } } } } } } }, { $addFields: { // fix sign "createdAt": { $multiply: [ "$createdAt.n", "$createdAt.sign" ] } } }, { $group: { _id: { ticket_type: "$ticket_type", createdAt: { $dateToString: { format: "%Y-%m-%d", date: { $add: [ new Date(0), "$createdAt" ] }} }, count: { $sum: 1 } } } }, { $sort: { "createdAt": 1 } } ], function(err, tickCount) { if (err) { console.log(err); } else { res.json(tickCount); } }); 
