Skip to content

Instantly share code, notes, and snippets.

@mderazon
Last active November 3, 2022 16:18
Show Gist options
  • Save mderazon/8201991 to your computer and use it in GitHub Desktop.
Save mderazon/8201991 to your computer and use it in GitHub Desktop.
Export all of Mongodb collections as csv without the need to specify fields
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;
@rmcpp
Copy link

rmcpp commented Dec 18, 2020

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;

@mail2shobz
Copy link

mail2shobz commented Apr 22, 2021

#!/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

@sandeep2244
Copy link

sandeep2244 commented Apr 29, 2021

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

@muhlucas
Copy link

muhlucas commented May 19, 2021

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]

@liufuzhu
Copy link

this shit does not work

@adamtaiti
Copy link

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!

@maksimsaroka
Copy link

@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;

@PratikNalage
Copy link

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"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment