How do I export (or copy and paste) particular columns of data? #Studio3T_AMA

Q: In the Table View, how do I export (or copy and paste) particular columns of data with Studio 3T?

A: There’s a couple of ways to select columns for copy and paste in the Table View. We’re going to show you two ways, a hands-on one using the Table View’s Hide Column, and a ready-to-automate one using MongoDB projection.

The big plus with using Projection to filter down your columns is that unlike hiding columns, the selection can work with Export, SQL Migration and other operations that can use a query as a source. Then you can move on from copying and pasting by hand, to fully automating the exporting of data.

Let’s start with the Table View’s Hide Column first.

Hiding Columns for Copy and Paste

Let’s start with a quick and dirty solution, because everyone loves those. First hide all the columns you don’t want in your export, using the Control+T (Command+T) show/hide column toggle. If you get a bit overzealous and hide everything, don’t worry. Here’s the customers example, completely hidden:

Notice that you can still select columns and when you do, the column name is displayed in the breadcrumb trail above. And Control+T (Command+T) is a toggle, so you can bring back any column to visibility. You can, of course, be sensible and only hide the columns you aren’t interested in. So let’s select the email and date of birth columns and make them visible again:

Now, let’s do Control+A (Command+A) to select all the rows in this view (all 200 in this case), and then pop up the context menu with a right click. Select Copy ➡ Copy Selected Documents as CSV (or any other copy type as required). The hidden columns will not be included in the copy. To restore your view, use the context menu on the table and select Column ➡ Make All Columns Visible.

Project Yourself

If you want to minimize the time you spend hiding columns, then you want to get MongoDB to help with its project option. Project lets you select the fields you want to display in your results and skip all the others. You can build a projection really quickly using the Visual Query Builder. Drag the fields you want to keep in your view into the drop area in the Projection section and they’ll automatically be included for you:

When you start a projection, remember every other field (apart from _id) will be excluded, so if its name is not in the list, it won’t appear. If you look at the Projection field in the Query Bar:

You’ll see how the Visual Query Builder translates that to MongoDB query language. You can, of course, manually type your own projections.

If we now run that query, we get this result:

Ah yes, remember how we mentioned that the _id field was, by default, included. Well, here it is. Quickest way to make it disappear is by just hiding the column with Control/Command+T.

Projecting _id

What if you included _id in the projection and set it to exclude like this:

That would be a projection of { _id:0, email:1, dob:1 }. What would happen then? Well, this:

Yes, the _id column still appears (because this is a table and _id fields are essential to the table’s functionality) but its contents are empty. If you Copy as CSV documents from this, the _id field will be included in the rows with an empty string value.

The solution again is to hide the column. But do note that if you flip over to Tree or JSON views you’ll find the _id is completely omitted from those views. It only appears in Tables as an artifact. Once you’ve hidden the _id you can select and Copy from the results.

As we mentioned at the start, using projection unlocks the ability to use Export, SQL Migrations and other 3T tools with your column selection. Then you can move on from copying and pasting only the visible documents by hand, to fully automated export of all the documents in the collection using Studio 3T’s Tasks and Task scheduler.

Your Copy, Your Paste

Essentially, both techniques work side by side. Hiding columns is great for ad-hoc copying of data. Using projections is great for starting and working with queries you’ll want to automate later. Whichever you choose, Studio 3T gives you options.