roboMongo export to csv output shows bson

Question

I don't have access to mongodb machine. So I cannot run a mongoexport command. Hence I am trying to get my query output to csv format.

Query in RoboMongo

var cursor = db.getCollection('fineProduct').find
(
        {"inbuilt.bookingReference" : { $exists : true }} , 

        {"_id":1,
        "Reference":1, 
        "inbuilt.bookingReference":1, 
        "inbuilt.status":1, 
        "purchase.fineSegments.departureDatetime":1, 
        "purchase.fineSegments.arrivalDatetime":1,
        "purchase.fineSegments.product.carriage.type":1,
        "purchase.fineSegments.pricing.amount":1,
        "purchase.fineSegments.pricing.currency":1
        }       
)
while (cursor.hasNext()) {
    var record = cursor.next();
    var output = "";
    for (var i in record) {
      output += record[i] + ",";
    };
    output = output.substring(0, output.length - 1);
    print(output);
}

Find Query output (in JSON) - 1 row only provided here

{
    "_id" : 10,
    "inbuilt" : {
        "status" : "VALIDATED",
        "bookingReference" : "2015900051789"
    },
    "purchase" : [ 
        {
            "fineSegments" : [ 
                {
                    "departureDatetime" : ISODate("2015-09-30T18:35:00.000Z"),
                    "arrivalDatetime" : ISODate("2015-09-30T19:17:00.000Z"),
                    "product" : {
                        "carriage" : {
                            "type" : "House"
                        }
                    },
                    "pricing" : {
                        "amount" : "339.00",
                        "currency" : "INR"
                    }
                }
            ]
        }
    ],
    "vendorReference" : "FIRE"
}

Output (in CSV)

10,[object BSON],[object BSON],FIRE
12,[object BSON],[object BSON],FIRE
13,[object BSON],[object BSON],FIRE
14,[object BSON],[object BSON],FIRE
15,[object BSON],[object BSON],FIRE
17,[object BSON],[object BSON],FIRE
18,[object BSON],[object BSON],FIRE
19,[object BSON],[object BSON],FIRE
20,[object BSON],[object BSON],FIRE

Is there any way to get [Object BSON] to string ?

Mongo db version 3.0.8 | robomongo version Robomongo 0.9.0-RC8

mongoexport works over network. If you can connect to mongodb with robomongo, you should be able to connect to it with mongoexport. — Alex Blex, Mar 07 '17 at 11:12
I don't have access to the machine installed (to ssh into the box) ! But robomongo can access the database through port as it is enabled IP:PORT by networking team :) — smilyface, Mar 07 '17 at 11:24
You don't need to ssh. run mongoexport on the same machine where you run robomongo with the same ip:port options. — Alex Blex, Mar 07 '17 at 11:29

Alex Blex · Answer 1 · 2017-03-07T12:04:47.880

CSV is flat 2d matrix, not able to hold complex structures. You need to project your documents to top-level primitives.

For your document it must be something like following (Mongo 3.2+):

db.getCollection('fineProduct').aggregate([
    {$project: {
        _id: 1,
        status: "$inbuilt.status",
        bookingReference: "$inbuilt.bookingReference",
        departureDatetime: { "$arrayElemAt": [
            { "$map": {
                "input": { "$slice": [
                    { "$map": {
                        "input": { "$slice": [ "$purchase", 0, 1 ] },
                        "as": "el",
                        "in": "$$el.fineSegments"
                    }},
                    0, 1
                ]},
                "as": "el",
                "in": { "$arrayElemAt": [ "$$el.departureDatetime", 0 ] }
             }},
             0
         ]},
        arrivalDatetime: { "$arrayElemAt": [
            { "$map": {
                "input": { "$slice": [
                    { "$map": {
                        "input": { "$slice": [ "$purchase", 0, 1 ] },
                        "as": "el",
                        "in": "$$el.fineSegments"
                    }},
                    0, 1
                ]},
                "as": "el",
                "in": { "$arrayElemAt": [ "$$el.arrivalDatetime", 0 ] }
             }},
             0
         ]},
         ..... etc
    }}
]);

and if your arrays have more than 1 element, or mongo version < 3.2 you will need to unwind them first:

db.getCollection('c').aggregate([
    {$unwind: "$purchase"},
    {$unwind: "$purchase.fineSegments"},
    {$project: {
        _id: 1,
        status: "$inbuilt.status",
        bookingReference: "$inbuilt.bookingReference",
        departureDatetime: "$purchase.fineSegments.departureDatetime",
        arrivalDatetime: "$purchase.fineSegments.arrivalDatetime",
        ..... etc
    }}

]);

It will result with CSV-friendly output:

{
    "_id" : 10.0,
    "status" : "VALIDATED",
    "bookingReference" : "2015900051789",
    "departureDatetime" : ISODate("2015-09-30T18:35:00.000Z"),
    "arrivalDatetime" : ISODate("2015-09-30T19:17:00.000Z"),
    ....
}

Thank you Alex. But I am getting exception : assert: command failed "errmsg" : "exception: invalid operator '$arrayElemAt'", "code" : 15999, | Mongo db version 3.0.8 | robomongo version Robomongo 0.9.0-RC8 — smilyface, Mar 07 '17 at 11:52
Fair enough, I have added an example for ancient versions of the db. — Alex Blex, Mar 07 '17 at 12:05
I tried it in another way and it worked well. Thank you for your help. I have added the answer. — smilyface, Mar 08 '17 at 05:19

score 0 · Accepted Answer · answered Mar 08 '17 at 05:32

This worked for me. Well, I am not sure whether this is a best way or not. As @Alex suggested, there might be other ways. I have added comments in the code so that to read and understand easily.

db.getCollection('fineProduct').find
(
        {"inbuilt.bookingReference" : { $exists : true }} , 

        {"_id":0, //NOT to print ID
        "vendorReference":1, //col1
        "inbuilt.bookingReference":1, //col2
        "inbuilt.status":1, //col3
        "purchase.fineSegments.departureDatetime":1, //col4
        "purchase.fineSegments.arrivalDatetime":1, //col5
        "purchase.fineSegments.product.carriage.type":1, //col6
        "purchase.fineSegments.pricing.amount":1, //col7
        "purchase.fineSegments.pricing.currency":1 //col8
        }       
)
.limit(3) //limit to 3 rows (remove this once done)
.forEach(function (x) {

    //col1 : "vendorReference"
    print(x.vendorReference + ",");

    //col2 : "inbuilt.bookingReference"
    print(x.inbuilt.bookingReference + ",");

    //col3 : "inbuilt.status"
    print(x.inbuilt.status + ",");

    //col4 : "purchase.fineSegments.departureDatetime"
    x.purchase.forEach(function (y) {
        if (y.fineSegments instanceof Array) {
            y.fineSegments.forEach(function (z) {
                print(z.departureDatetime + ",");
            });
        }
    });

    //col5 : "purchase.fineSegments.arrivalDatetime"
    x.purchase.forEach(function (y) {
        if (y.fineSegments instanceof Array) {
            y.fineSegments.forEach(function (z) {
                print(z.arrivalDatetime + ",");
            });
        }
    });

    //col6 : "purchase.fineSegments.product.carriage.type"
    x.purchase.forEach(function (y) {
        if (y.fineSegments instanceof Array) {
            y.fineSegments.forEach(function (z) {
                print(z.product.carriage.type + ","); // used dot as it is not in array with closed bracket
            });
        }
    });

    //col7 : "purchase.fineSegments.pricing.amount"
    x.purchase.forEach(function (y) {
        if (y.fineSegments instanceof Array) {
            y.fineSegments.forEach(function (z) {
                print(z.pricing.amount + ",");
            });
        }
    });

    //col8 "purchase.fineSegments.pricing.currency"
    x.purchase.forEach(function (y) {
        if (y.fineSegments instanceof Array) {
            y.fineSegments.forEach(function (z) {
                print(z.pricing.currency);
            });
        }
    });

    print("#line_end#");
});

Output will not be formatted one. The 'print' command always writes with a new line !. So, after getting the output you will have to format it with a editor (like notepad++)..

Last output

x1,y1,C,Thu Oct 01 2015,Thu Oct 01 2015,FIRE,233,INR
x2,y3,A,Thu Oct 01 2015,Thu Oct 01 2015,FIRE,433,US
x5,y4,B,Thu Oct 01 2015,Thu Oct 01 2015,FIRE,890,INR

roboMongo export to csv output shows bson

2 Answers2