What problems

Robo 3T (aka Robomongo) is a simple little query tool for MongoDB. It's perhaps not crazy convenient, and it looks like MySQL tools used to look about a decade ago, but it does its job well, and it's free.

Due to its simplicity some functionality is either not there, or it's unclear how to get it. Two problems I faced recently were:

• How to return more than default 50 records
• How to export returned records to CSV

What solutions

50 records limit

The more-than-50-records problem had a fairly straightforward solution: add a batch size declaration before the query:

DBQuery.shellBatchSize = 500;

So setting this to something really high does the trick.

Note: it's persistent, so to return it to 50 you need to set it to 50 explicitly, otherwise it'd use the defined batch size even when the declaration is removed.

Export to CSV

This other problem of getting results in CSV format was more interesting - at first I tried to find online services for that, though most of the JSON-to-CSV solutions only work with a complete JSON object (and not separate JSON blobs per document). There's one resource that supports this case - this JSON to CSV Converter - but it starts asking for money pretty quick, and its copy-to-clipboard function seems to be broken.

After some extra search, turned out that there's a way of extending Robo 3T functionality with Javascript, so missing functionality could be added to it (to some extent).

Essentially if a file .robomongorc.js exists in home directory, Robo 3T will execute it on start (so restart it to load it), and the functions defined in this file will be available for the query processing.

From the article on Studio3T wiki, adding the following function to ~/.robomongorc.js file adds a CVS conversion functionality:

// Export to CSV function
function toCSV (deliminator, textQualifier)
{
var count = -1;
var headers = [];
var data = {};

    var cursor = this;

    deliminator = deliminator == null ? ',' : deliminator;
    textQualifier = textQualifier == null ? '\"' : textQualifier;

    while (cursor.hasNext()) {

        var array = new Array(cursor.next());

        count++;

        for (var index in array[0]) {
            if (headers.indexOf(index) == -1) {
                headers.push(index);
            }
        }

        for (var i = 0; i < array.length; i++) {
            for (var index in array[i]) {
                data[count + '_' + index] = array[i][index];
            }
        }
    }

    var line = '';

    for (var index in headers) {
        line += textQualifier + headers[index] + textQualifier + deliminator;
    }

    line = line.slice(0, -1);
    print(line);

    for (var i = 0; i < count + 1; i++) {

        var line = '';
        var cell = '';
        for (var j = 0; j < headers.length; j++) {
            cell = data[i + '_' + headers[j]];
            if (cell == undefined) cell = '';
            line += textQualifier + cell + textQualifier + deliminator;
        }

        line = line.slice(0, -1);
        print(line);
    }
}
DBQuery.prototype.toCSV=toCSV; //regular find
DBCommandCursor.prototype.toCSV=toCSV; //aggregates

To use it, add .toCSV() at the end of the query, like e.g.

db.getCollection('somecollection').find({}).toCSV()

Neat!