Export all of Mongodb collections as csv without the need to specify fields

mongo-dump-csv.sh

OIFS=$IFS;
IFS=",";

# fill in your details here
dbname=DBNAME
user=USERNAME
pass=PASSWORD
host=HOSTNAME:PORT

# first get all collections in the database
collections=`mongo "$host/$dbname" -u $user -p $pass --eval "rs.slaveOk();db.getCollectionNames();"`;
collections=`mongo $dbname --eval "rs.slaveOk();db.getCollectionNames();"`;
collectionArray=($collections);

# for each collection
for ((i=0; i<${#collectionArray[@]}; ++i));
do
    echo 'exporting collection' ${collectionArray[$i]}
    # get comma separated list of keys. do this by peeking into the first document in the collection and get his set of keys
    keys=`mongo "$host/$dbname" -u $user -p $pass --eval "rs.slaveOk();var keys = []; for(var key in db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]) { keys.push(key); }; keys;" --quiet`;
    # now use mongoexport with the set of keys to export the collection to csv
    mongoexport --host $host -u $user -p $pass -d $dbname -c ${collectionArray[$i]} --fields "$keys" --csv --out $dbname.${collectionArray[$i]}.csv;
done

IFS=$OIFS;

can we do that in a .bat file to be executed from cmd ??

It wants to connect to 127.0.0.1 even after I set the variables:

Stephens-MacBook-Pro:mongo stephenrichards$ ./export.sh
exception: connect failed
exporting collection MongoDB shell version: 2.6.5
connecting to: ibmavnet
2015-03-12T11:34:06.875+0000 warning: Failed to connect to 127.0.0.1:27017
connected to: ds035240.mongolab.com:35240
exported 1 records
exporting collection reason: errno:61 Connection refused
2015-03-12T11:34:06.876+0000 Error: couldn't connect to server 127.0.0.1:27017 (127.0.0.1)

Try with commenting out line no. 12

Just Comment "user=USERNAME", "pass=PASSWORD" and remove "-u $user" "-p $pass" will work fine.

#!/bin/bash
OIFS=$IFS;
IFS=",";
# fill in your details here
dbname=DATABASE
user=USER
pass=PASSOWRD
host="localhost"
port="27017"
#Change this to the DB you are dumping if you have the user created at the DB level.
authdb="root"

# first get all collections in the database
collectionArray=$(mongo $dbname --host $host --port $port -u $user -p $pass --authenticationDatabase $authdb --eval 'db.getCollectionNames().join('');' --quiet);

# for each collection
for col in $collectionArray;
do
    echo 'exporting collection' $col
    # get comma separated list of keys. do this by peeking into the first document in the collection and get his set of keys
    keys=`mongo $dbname --host $host --port $port -u $user -p $pass --authenticationDatabase $authdb --eval "function z(c,e){var a=[];var d=Object.keys(c);for(var f in d){var b=d[f];if(typeof c[b]==='object'){var g=[],h=z(c[b],e+'.'+b);a=g.concat(a,h);}else a.push(e+'.'+b);}return a;}var a=[],b=db.$col.findOne({}),c=Object.keys(b);for(var i in c){var j=c[i];if(typeof b[j]==='object'&&j!='_id'){var t1=[],t2=z(b[j],j);a=t1.concat(a,t2);}else a.push(j);}a.join(',');" --quiet`
    # now use mongoexport with the set of keys to export the collection to csv
    mongoexport --host $host --port $port -u $user -p $pass --authenticationDatabase $authdb -d $dbname -c $col --fields "$keys" --type=csv --out $dbname.$col.csv --authenticationDatabase wowtokenio;
done

IFS=$OIFS;

I modified the code a bit:

1. Corrected the connection string format to account for mongodb's changes.
2. Modified the javascript to include keys of sub-objects within the document.

{
    "_id" : "5a893606c66f97364f7564bb",
    "regionName" : "KR",
    "regionNameBlizzard" : "kr",
    "subRegionCount" : 1,
    "regionCollectionName" : "krPrices",
    "regionNameFriendly" : "Korea",
    "subRegions" : {
            "subRegion_KRW" : {
                    "subRegionISO" : "KR",
                    "subRegionName" : "Korea",
                    "subRegionCurrencyCode" : "KRW",
                    "subRegionCurrencyName" : "South Korean won",
                    "subRegionCurrencySymbol" : "₩",
                    "subRegionBNetPurchasePrice" : 22000,
                    "subRegionBNetBalanceValue" : 0,
                    "subRegionBNetExchangeSupported" : false
            }
    }
}

The above document yields the below fields:

[ '_id',
  'regionName',
  'regionNameBlizzard',
  'subRegionCount',
  'regionCollectionName',
  'regionNameFriendly',
  'subRegions.subRegion_KRW.subRegionISO',
  'subRegions.subRegion_KRW.subRegionName',
  'subRegions.subRegion_KRW.subRegionCurrencyCode',
  'subRegions.subRegion_KRW.subRegionCurrencyName',
  'subRegions.subRegion_KRW.subRegionCurrencySymbol',
  'subRegions.subRegion_KRW.subRegionBNetPurchasePrice',
  'subRegions.subRegion_KRW.subRegionBNetBalanceValue',
  'subRegions.subRegion_KRW.subRegionBNetExchangeSupported' ]

how can we do the same thing using javascript..??

Simplified further to avoid messing with IFS, fixed small bug on "null" in z() and modified to work on a mongo docker container

collections=$(docker exec mongo mongo $DB --quiet --eval 'rs.slaveOk();db.getCollectionNames().join(" ");')
for col in $collections; do
    echo "Exporting collection $col"
    # get comma separated list of keys. do this by peeking into the first document in the collection and get his set of keys
    keys=$(docker exec mongo mongo $DB --quiet --eval "function z(c,e){if(c===null){return e};var a=[];var d=Object.keys(c);for(var f in d){var b=d[f];if(typeof c[b]==='object'){var g=[],h=z(c[b],e+'.'+b);a=g.concat(a,h);}else a.push(e+'.'+b);}return a;}var a=[],b=db.$col.findOne({}),c=Object.keys(b);for(var i in c){var j=c[i];if(typeof b[j]==='object'&&j!='_id'){var t1=[],t2=z(b[j],j);a=t1.concat(a,t2);}else a.push(j);}a.join(',');")
    # now use mongoexport with the set of keys to export the collection to csv
    docker exec mongo mongoexport --quiet -d $DB -c $col --fields "$keys" --type=csv --out "/dump/$col.csv"
done

2020-06-08T03:35:32.886-0800 E QUERY [js] SyntaxError: missing } after function body @(shell eval):1:388

It returned the above error in output file, do you have any idea about what is the problem here?

If the name of your collection had a hyphen the export will return undefined.

The code that fixed it.

#!/bin/bash
OIFS=$IFS;
IFS=",";
# fill in your details here
dbname=DATABASE
user=USER
pass=PASSOWRD
host="localhost"
port="27017"
#Change this to the DB you are dumping if you have the user created at the DB level. e.g AWS cluster is admin
authdb="root"

# first get all collections in the database
collectionArray=$(mongo $dbname --host $host --port $port -u $user -p $pass --authenticationDatabase $authdb --eval 'db.getCollectionNames().join('');' --quiet);

for col in $collectionArray;
do
    echo 'exporting collection' ${col}
    # get comma separated list of keys. do this by peeking into the first document in the collection and get his set of keys
    keys=`mongo $dbname --host $host --port $port -u $user -p $pass --authenticationDatabase $authdb --eval "function z(c,e){if(c===null || c === undefined){return e};var a=[];var d=Object.keys(c);for(var f in d){var b=d[f];if(b != undefined && c != undefined && typeof c[b]==='object'){var g=[],h=z(c[b],e+'.'+b);a=g.concat(a,h);}else a.push(e+'.'+b);}return a;}var a=[],b=db['$col'].findOne({}),c=Object.keys(b);for(var i in c){var j=c[i];if(typeof b[j]==='object'&&j!='_id'){var t1=[],t2=z(b[j],j);a=t1.concat(a,t2);}else a.push(j);}a.join(',');" --quiet`
    # now use mongoexport with the set of keys to export the collection to csv
    mongoexport --host $host --port $port -u $user -p $pass -d $dbname --authenticationDatabase $authdb -c "$col" --fields "$keys" --type csv --out $dbname."$col".csv;
done

IFS=$OIFS;

when "eval" the code without "--quiet" symbol, some unexpected words will occours in your result. So I fixed this problem and my solution is below:

OIFS=$IFS;
IFS=",";

# fill in your details here
dbname=yourdbname
host=127.0.0.1:27017

# first get all collections in the database
collections=`mongo "$host/$dbname" --quiet  --eval "rs.slaveOk();db.getCollectionNames();"`;
collections=`mongo --quiet $dbname --eval "rs.slaveOk();var names=db.getCollectionNames().join(','); names"` ;
echo $collections;

collectionArray=($collections);

# for each collection
for ((i=0; i<${#collectionArray[@]}; ++i));
do
    echo 'exporting collection' ${collectionArray[$i]}
    # get comma separated list of keys. do this by peeking into the first document in the collection and get his set of keys
    keys=`mongo "$host/$dbname" --eval "rs.slaveOk();var keys = []; for(var key in db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]) { keys.push(key); }; keys.join(',');" --quiet`;
    echo $keys;
    # now use mongoexport with the set of keys to export the collection to csv
    mongoexport --host $host -d $dbname -c ${collectionArray[$i]} --fields "$keys" --type=csv --out ${collectionArray[$i]}.csv;
done

IFS=$OIFS;

#!/bin/bash
OIFS=$IFS;
IFS=",";
#Accepting Entry form the user
printf 'Enter the DB name you want convert to CSV:'
read -r dbname
dbname=$dbname
host=127.0.0.1:27017

# first get all collections in the database
collections=mongo "$host/$dbname" --quiet --eval "rs.secondaryOk();db.getCollectionNames();";
collections=mongo --quiet $dbname --eval "rs.secondaryOk();var names=db.getCollectionNames().join(','); names" ;
echo $collections;

collectionArray=($collections);

# for each collection
for ((i=0; i<${#collectionArray[@]}; ++i));
do
echo 'exporting collection' ${collectionArray[$i]}
keys=mongo "$host/$dbname" --eval "rs.secondaryOk();var keys = []; for(var key in db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]) { keys.push(key); }; keys.join(',');" --quiet;
echo $keys;
# now use mongoexport with the set of keys to export the collection to csv
mongoexport --host $host -d $dbname -c ${collectionArray[$i]} --fields "$keys" --type=csv --out ${collectionArray[$i]}.csv --forceTableScan;
done

IFS=$OIFS;
#Tested in latest version of Mongo DB it will successfully generate csv of all colections

Hey @muhlucas , what if i want get keys of last inserted docs ? , where i can add sort ?

Hey @muhlucas , what if i want get keys of last inserted docs ? , where i can add sort ?

@sandeep2244 You could try to edit the filter to get keys.

The below code is part of the current script:

keys=mongo "$host/$dbname" --eval "rs.slaveOk();var keys = []; for(var key in db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]) { keys.push(key); }; keys.join(',');" --quiet; echo $keys

Change the following part to add your filter and get the last keys of a collection.
db.${collectionArray[$i]}.find().sort({_id: -1}).limit(1)[0]

this shit does not work

I tried it and it is working fine, except for date fields. They are not being exported! Any idea why is that?
Appreciate any help!

@adamtaiti here is code with fix for exporting dates:

keys=mongo $dbname --host "$host" --port "$port" -u "$user" -p "$pass" --authenticationDatabase "$authdb" --ssl --eval "function z(c,e){if(c===null || c === undefined || c instanceof Date){return e};var a=[];var d=Object.keys(c);for(var f in d){var b=d[f];if(b != undefined && c != undefined && typeof c[b]==='object'){var g=[],h=z(c[b],e+'.'+b);a=g.concat(a,h);}else a.push(e+'.'+b);}return a;}var a=[],b=db['$col'].findOne({}),c=Object.keys(b);for(var i in c){var j=c[i];if(typeof b[j]==='object'&&j!='_id'){var t1=[],t2=z(b[j],j);a=t1.concat(a,t2);}else a.push(j);}a.join(',');" --quiet;

I'm unable to retrieve the Id, because of the following error
"2022-02-08T13:30:02.767-0500 I CONTROL [thread1] machdep.cpu.extfeatures unavailable _id"