Node.js Fetch last week data

Question

I have mongoose schema structured somewhat like this:

{
    "_id": {
        "$oid": "59d00283893f4500127271f6"
    },
    "__v": 0,
    "bills": [
        {
            "timestamp": 1513539218509,
            "table_no": 2,
            "user_id": {
                "$oid": "59cf912782b85b0012860ecc"
            },
            "order_id": {
                "$oid": "5a36c5979a54980014f06787"
            },
            "_id": {
                "$oid": "5a36c6929a54980014f0678b"
            },
            "disc_amount": 320,
            "amount": 320
        },
        {
            "timestamp": 1513539299486,
            "table_no": 2,
            "user_id": {
                "$oid": "59cf912782b85b0012860ecc"
            },
            "order_id": {
                "$oid": "5a36c6c99a54980014f0678c"
            },
            "_id": {
                "$oid": "5a36c6e39a54980014f0678f"
            },
            "disc_amount": 160,
            "amount": 160
        },
        {
            "timestamp": 1513540879109,
            "table_no": 2,
            "user_id": {
                "$oid": "59cf912782b85b0012860ecc"
            },
            "order_id": {
                "$oid": "5a36ccfb9a54980014f06790"
            },
            "_id": {
                "$oid": "5a36cd0f9a54980014f06793"
            },
            "disc_amount": 320,
            "amount": 320
        },
        {
            "timestamp": 1513540986507,
            "table_no": 2,
            "user_id": {
                "$oid": "59cf912782b85b0012860ecc"
            },
            "order_id": {
                "$oid": "5a36cd639a54980014f06794"
            },
            "_id": {
                "$oid": "5a36cd7a9a54980014f06797"
            },
            "disc_amount": 240,
            "amount": 320
        }
    ]
}

From the subschema bills i want to fetch only those bills which are a week old only.

My first Question is, is it a right way to first find Schema by id, then get its bills and in bills array do processing to fetch only last week data. I guess that won't be efficient because subschema bills can be too large, therefore, it won't be a good idea to fetch whole array. So, what would be the proper way?

And Second question, is there more appropriate way to create schema and do query.(I created subschema bills, so that all bills related to particular id remain at one place, so easy to query. Is this efficient?).

Open to all kind of suggestion. Thanks in advance.

Use [$filter](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) to filter bills on query criteria. — s7vr, Dec 27 '17 at 16:18

Patrick R · Answer 1 · 2017-12-28T07:07:55.153

You can create model for your collection in mongoose and use it as following:

var start = new Date(new Date().getTime() - (7 * 60 * 60 * 24 * 1000));  // get date of last week
  Bill.find({
    'bills.timestamp' : {"$gte": start.getTime()}
  }, {
    // Only include the subdocument(s) that matched the query.
    'bills.$'    : 1
  }, function(err, data){
    console.log(data)
  });

Model:

var mongoose = require('mongoose');

/**
 * Bill Mongo DB model
 * @name billModel
 */
var billModel = function () {

    var billSchema = mongoose.Schema({

    bills:{ type : Array , "default" : [] }

    });


  return mongoose.model('Bill', billSchema);
};

module.exports = new billModel();

For single id, below code can be used:

Bill.aggregate([
        {
            $match: {
                '_id': new mongoose.Types.ObjectId('5968dcf965854c7817606014')
            }
        },
        {
          $project: {
             bills: {
                $filter: {
                   input: "$bills",
                   as: "bill",
                   cond: { $gte: [ "$$bill.timestamp", start.getTime() ] }
                }
             }
          }
        },
    ], function (err, result) {
        if (err) {
            console.log(err);
        } else {
            res.json(result);
        }
    });

I want bills of last week for a particular id. Let say for _id "123" find all bills from last week. Not overall last week bills. — Rishabh Chandel, Dec 27 '17 at 15:53
Hi, see updated answer, I have updated code for single id. Hope it helps — Patrick R, Dec 28 '17 at 07:08
I already upvoted, but that's not what i was looking for. I wanted to know to was my schema efficient, which is not. As i mentioned, bills subschema can be too large, it is not a good practise to embed inside Schema. Thanks though. — Rishabh Chandel, Dec 29 '17 at 09:33

Node.js Fetch last week data

1 Answers1