In this post, we’ll show you how to compare MongoDB collections located in different databases and/or servers, analyze differences between their documents and fields, and sync your data using Studio 3T’s Data Compare & Sync feature.
- The Importance of Comparing MongoDB Databases
- Choose Your Source and Target Connections
- Choose MongoDB Collections to Compare
- Configure Your Data Comparison
- Run the Analysis
- Spot the Difference(s)
- Export Comparison Results to CSV
- Sync MongoDB Documents
- Sync MongoDB Fields
- Sync MongoDB Data via In-Place Editing
The Importance of Comparing MongoDB Databases
There are a number of situations where being able to conveniently analyze the differences between two database installations is extremely handy.
For example, you have a problem with your production system. All you know about it is that the problem lies somewhere in the database, but WHERE in the database? It worked fine yesterday, so if you compare the current database with yesterday’s backup, most likely you’ll find out what the problem is.
Additionally, it could be time to release an upgrade to the production system, but there are new configuration parameters required. The upgrade can be performed with a calm confidence when you can easily compare against the staging server and clearly see which parameters need to be introduced, changed or removed.
Or worse: an accidental update has removed vital configuration from the system, and it’s now crashing. You knew you should have taken a backup, but there just wasn’t time. And besides it was just a trivial change, so what could possibly go wrong?
But now, all you can do is sit and stare at your screen in horror and gulp down the bitter taste of regret. Unless of course, you can compare the differences and quickly identify which settings have been lost, and carry out an informed repair before anyone realizes your terrible blunder! (Of course, this NEVER HAPPENS on production systems, right? 🙂 )
More generally, being able to see how your data is changing is very powerful. Not only does it let you gain insight to your data, but it also provides confident control over the changes when you can see them explicitly and edit them directly.
Choose Your Source and Target Connections
First of all, download our MongoDB GUI in case you haven’t already. It’s available for Windows, Mac, and Linux.
Start the app, and click on Compare on the toolbar.
This will open up a view where we can select source and target connections, which looks like this:
Under Source Connection, click Connect to Server.
If this is your first time using Studio 3T, you’ll be presented with the Connection Manager where you can create a new MongoDB connection.
If you’ve already created a connection, you can simply select it.
Under Target Connection, click Connect to Server and similarly select or create the target connection.
The documents from the source connection will be compared to documents from the target connection. The source and target can be the same if need be.
OK, now we have you source and target connections selected. In the image below, we’re comparing source connection test-db against target connection test-db, located on two different servers.
Choose MongoDB Collections to Compare
Now, let’s select the specific collections we want to compare.
To do this, simply drag the source database on top of the target database, and voilà! All collections with the same name in the source and target databases are automatically selected for comparison (highlighted in yellow in the screenshot below).
Of course, it’s not mandatory that collections need to have the same name. For example, if you need to compare the source collection current_customers with the target collection existing_customers, simply drag current_customers on top of existing_customers and it will be added to the comparison (highlighted in purple).
If you want to remove a particular pair of collections from the selection, simply right click it and choose Remove from comparison list. Alternatively, click the Remove button on the right of the comparison table.
Configure Your Data Comparison
Studio 3T’s Data Compare and Sync feature also lets you configure data comparison settings, which can come in handy in case you only want to compare certain fields, filter based on certain criteria, etc.
To start, click on Configure to the right of the table. This will open up the Comparison Configuration window which has four tabs: Match On, Compare, Filter, and Projection.
Match On lets you define which fields source and target documents need to match on.
This is automatically set to Match on ‘_id’, but you can also choose to match on selected fields. Just remember to choose fields that 1.) have unique values and 2.) whose collections have an index.
Under the Compare tab, you can:
- Define how many documents should be read per batch for comparison. By default this is set to 1,000 documents.
- Choose whether to treat number types (e.g. Int32, Int64) as equal or not
- Specify which fields to compare and which fields to ignore. By default, all fields are compared.
Filter lets you enter a filter criteria which will limit the set of documents to compare.
Projection lets you limit the fields to compare.
Run the Analysis
OK, now that we’ve selected what we want to compare, hit the Run comparison button right above the table.
When the run is completed, a tab will open for each pair of collections. Let’s take a closer look at accounts.
In the image above, we can see the comparison took 792 milliseconds and found seven differences in total: five differing documents, one document that only appeared on the source side, and one document that only appeared on the target side.
The Overview tab shows exactly what was compared. This is useful if we save the results to come back to them later, by which time the comparison configuration may have been updated. The overview page of the results provides a permanent record of what was actually compared.
Spot the Difference(s)
OK, now it’s time for the main course! Click on the Differences tab.
Differing documents are highlighted in yellow, source-only documents in green and target-only in red, though in nice pastel shades that are easy on the eye.
You can further filter this view by clicking on View Options, which groups the differences by type (e.g. show differing documents only).
With Studio 3T Release 5.7, you can even choose to Show differing fields only for a cleaner, more targeted overview:
Now let’s expand a document. (Tip: You can expand or collapse all documents in one right-click.)
Here we can see the same highlighting pattern: yellow shows fields that are different, green shows source-only fields, and red shows target-only fields.
If you’re comparing differing documents, identical fields will be shown in the normal alternating grey and white lines, or whichever colors are normal for your particular OS).
Export Comparison Results
With Studio 3T 2018.2, you can now also export your comparison results to a CSV file for a more convenient data analysis.
Simply click on the “Export” button under the Differences tab and configure the export settings as needed.
Sync MongoDB Documents
We realize our source-only document should indeed be on the target collection, so we right click Copy Document to Target and confirm the copy operation.
Alright. Now we realize the target-only document is old and should no longer be present on the target.
No problem: We simply right-click and select Delete Document.
Sync MongoDB Fields
OK, we’ve done some syncing at document level, now we sync individual fields. View Options > Show Differing Fields Only will really come in handy here.
Looking at the first document, it looks like ‘pet:manatee’ on the target side is incorrect, but instead should be ‘pet:cat’ as it is on the source side.
Right click on either ‘pet:manatee’ or ‘pet:cat’. Since the field exists on both source and target, you can choose to either ‘Copy Field from Source’ or ‘Copy Field to Target’, respectively.
You might also come across missing fields in the source document that should be copied over from the target document.
In this case, you would simply right click and select ‘Copy Field to Source’ if you clicked on the target side, or ‘Copy Field from Target’ if you clicked on the source side.
Studio 3T’s Data Compare and Sync lets you do a bi-directional sync on the same document, in that some changes are copied from the target document to the source, and other changes from the source to the target. Pretty neat, eh? 🙂
Sync MongoDB Data via In-Place Editing
Alright, we’re done with the veg and potatoes, let’s center in on the steak!
For small changes, you don’t even need to invoke the right-click menu. Simply double-click a field and edit it directly – the diff status is updated in real time.
It’s Been a Pleasure
And that concludes our journey through how to compare MongoDB collections! I hope this tutorial on how to diff MongoDB has left you a little bit more powerful than when our journey first began.
Complement your new data comparison skills by checking out Schema Explorer, another nifty Studio 3T feature that will help tremendously in your data analysis. And if you haven’t downloaded our MongoDB GUI yet, here’s another chance 🙂
Editor’s Note: This post was originally published in May 2014.