Convert date to MongoDB ISODate format in JavaScript using Moment JS

uses ISODate as their primary date type. If you want to insert a date object into a MongoDB collection, you can use the Date() shell method.

You can specify a particular date by passing an ISO-8601 date string with a year within the inclusive range 0 through 9999 to the new Date() constructor or the ISODate() function. These functions accept the following formats:

• new Date(““) returns the ISODate with the specified date.
• new Date(““) specifies the datetime in the client’s local timezone and returns the ISODate with the specified datetime in UTC.
• new Date(““) specifies the datetime in UTC and returns the ISODate with the specified datetime in UTC.
• new Date() specifies the datetime as milliseconds since the Unix epoch (Jan 1, 1970), and returns the resulting ISODate instance.

If you are writing code in JavaScript and if you want to pass a JavaScript date object and use it with MongoDB client, the first thing you do is convert JavaScript date to MongoDB date format (ISODate). Here’s how you do it.

var today = moment(new Date()).format('YYYY-MM-DD[T00:00:00.000Z]');
console.log("Next day -- " + (reqDate.getDate() + 1))
var d = new Date();
d.setDate(reqDate.getDate() + 1);
var tomorrow = moment(d).format('YYYY-MM-DD[T00:00:00.000Z]');

You can pass today and tomorrow object to MongoDB queries with new Date() shell method.

  MongoClient.connect(con, function (err, db) {
    if (err) throw err
    db.collection('orders').find({ "order_id": store_id, "orderDate": { 	"$gte": new Date(today), "$lt": new Date(tomorrow) } 			}).toArray(function (err, result) {
      console.log(result);
      if (err) throw err
      res.send(result);
    })
  })

How to convert string fields to date fields in MongoDB

It’s normal that we migrate Date fields from other database engines or maybe import information from CSV files. At the end of any migration, one of the most common mistakes is store dates as strings, instead of a Date field that could be understood for your new database engine.

In terms of MongoDB, storing dates as string limit us to use Date functions to filter or process that information in a proper way. Here I want to show you how to solve that issue and don’t get crazy in the process.

Imagine that you have a collection of documents named “time” with a property called “date”, the following script will traverse all documents in that collection to update each document to replace the property date as a string using the Date format.

db.getCollection('time').find({})
    .forEach( function(item){
        if (typeof(item.date) == "string"){
            print(item.date);
            item.date = new Date(item.date);
            db.getCollection('time').save(item);
        }
    }
);

You can run first the script with the save part commented out and use the print to debug that the script is doing what do you expect of it.

As a minor change, you can choose to use ISODate instead of Date as you can see below.

db.getCollection('time').find({})
    .forEach( function(item){
        if (typeof(item.date) == "string"){
            print(item.date);
            //item.date_updated = new ISODate(item.date_updated);
            //db.items.save(item)
        }
    }
);

Now you can use the normal Date functions to select or filter your data, here an example.

db.getCollection('time').find({
	date: { $lte: new Date('11/15/2017') }
});

I hope this blog post helps you to handle your data in Mongo DB easier and help you with the transition to Mongo DB.

How to detect and delete orphaned documents in Mongo DB

In any database relational or not, usually, we link tables or collections ( in the case of Mongo DB). But, one process that I found a little tricky to do on my firsts days with mongo. Was how to detect documents that are not related to anyone, but it’s is expected to be related, if not maybe we want to delete those documents.

Let me set an example. Let’s imagine that we have a collection named clients and another collection named projects, and projects are related to clients using the Client Id Document.

As you maybe know each document in Mongo DB could have a different structure by his own. So, we cannot warranty that all documents have the same fields, but let imagine our client documents looks like this.

Using the following query, we could the first document in our collection

db.clients.findOne();

As a result, we could get something like this

{
"_id" : "24Ne5krwnba8hxHRc",
"name" : "ACME",
"createdAt" : ISODate("2017-11-08T19:23:09.730Z"),
"updatedAt" : ISODate("2019-02-21T16:15:27.796Z"),
"country" : "GB",
"email" : "info@example.com",
}

We could do the same with the collection projects:

db.projects.findOne();

Below an example of a project document

{
	"_id" : "2W9anDLvkB8td7NEu",
	"name" : "Policy Book",
	"client" : "uBXNLm2MdgMXkWcRB",
	"createdAt" : ISODate("2017-12-05T19:01:43.914Z"),
	"updatedAt" : ISODate("2017-12-20T19:37:49.669Z"),
}

Now, imagine that for some strange reason we have the idea that we have clients orphaned of projects. In other words, clients without any projects, and for this example, we will say those clients are required to be removed.

Below I will show you how to accomplish this task.

Creating a cursor

In Mongo DB a cursor is a query result, that you can iterate using these cursors methods.

var cursor = db.getCollection('clients').aggregate([
    {
      $lookup:
        {
          from: "projects",
          localField: "_id",
          foreignField: "client",
          as: "matched_docs"
        }
   },
   {
      $match: { "matched_docs": { $eq: [] } }
   }
]);

Now, let me dissect a little bit the query itself.

Aggregate

The aggregate method allows you to operate in one or more collection to execute a plan or pipeline of a query to get the information needed.

In our case, our pivot collection is clients,and we will run the aggregation on this collection.

$Lookup

In Mongo DB, a pipeline is a combination of stages that are executed in sequence.

The $lookup stage allows us to implement a left outer join between our pivot collection and another collection.

Let’s review in detail

$lookup:
        {
          from: "projects",
          localField: "_id",
          foreignField: "client",
          as: "matched_docs"
        }
}

The snippet about said, we want to do a left outer join between clients out pivot table and the collection projects, using the fields clients._id and project.client as a cross-reference to do the join.

In the end, the result is stored in a new field in result document using the name matcheddocs_ you can use any name that makes more sense to you.

Filtering

After doing the left outer join we need to filter the relationship to be sure we will only get those clients without projects, for that we will use the stage match in combination with the operator $eq.

{
      $match: { "matched_docs": { $eq: [] } }
}

This filtering will return only the relations with an empty array of documents, which is equal to orphaned clients.

Getting clients to delete

Using the cursor, we could iterate it to create a list of clients Ids ready to be removed.

var client_ids = cursor.map(function (doc) { return doc._id; });

Remove clients

Now we need to iterate our array of client ids to remove those clients using the collection method remove.

db.getCollection('clients').remove({"_id": { "$in": client_ids }});

I hope that you now have a better understanding of how to fetch information from Mongo DB and operate with the results.

Happy queries.

Reference

mongodb-isodate-format
convert string fields to date fields in MongoDB
detect and delete orphaned documents in Mongo DB