mongodb导出指定条件数据

一、Mongodb导出工具mongoexport

Mongodb中的mongoexport工具可以把一个collection导出成JSON格式或CSV格式(类似于表格的形式)的文件。可以通过参数指定导出的数据项，也可以根据指定的条件导出数据。
mongoexport具体用法

$mongoexport --help

general options:
--help print usage
--version print the tool version and exit

verbosity options:
-v, --verbose more detailed log output (include multiple times for more verbosity, e.g. -vvvvv)
--quiet hide all log output

connection options:
-h, --host= mongodb host to connect to (setname/host1,host2 for replica sets)
--port= server port (can also use --host hostname:port)

ssl options:
--ssl connect to a mongod or mongos that has ssl enabled
--sslCAFile= the .pem file containing the root certificate chain from the certificate authority
--sslPEMKeyFile= the .pem file containing the certificate and key
--sslPEMKeyPassword= the password to decrypt the sslPEMKeyFile, if necessary
--sslCRLFile= the .pem file containing the certificate revocation list
--sslAllowInvalidCertificates bypass the validation for server certificates
--sslAllowInvalidHostnames bypass the validation for server name
--sslFIPSMode use FIPS mode of the installed openssl library

authentication options:
-u, --username= username for authentication
-p, --password= password for authentication
--authenticationDatabase= database that holds the user's credentials
--authenticationMechanism= authentication mechanism to use

namespace options:
-d, --db= database to use
-c, --collection= collection to use

output options:
-f, --fields= comma separated list of field names (required for exporting CSV) e.g. -f "name,age"
--fieldFile= file with field names - 1 per line
--type= the output format, either json or csv (defaults to 'json')
-o, --out= output file; if not specified, stdout is used
--jsonArray output to a JSON array rather than one object per line
--pretty output JSON formatted to be human-readable

querying options:
-q, --query= query filter, as a JSON string, e.g., '{x:{$gt:1}}'
-k, --slaveOk allow secondary reads if available (default true)
--forceTableScan force a table scan (do not use $snapshot)
--skip= number of documents to skip
--limit= limit the number of documents to export
--sort= sort order, as a JSON string, e.g. '{x:1}'

 

举例：

导出test_db数据库，test_collection集合，uploadDate（日期型）为2019-12-02的数据，导出字段有uploadDate,length,filename，导出文件格式为csv格式

./mongoexport -h 172.128.111.56 --port 27017 -u admin -p admin -d test_db -c test_collection --type=csv -f uploadDate,length,filename -q '{"uploadDate":{"$lte":new Date("2019-12-02T16:00:00.000Z"),"$gt":new Date("2019-12-01T16:00:00.000Z")}}' -o /home/test/mongodb/test_20191202.csv

 

有时如果数据需要认证权限，可能会提示如下错误信息：

Failed: error connecting to db server: server returned error on SASL authentication step: Authentication failed.

原由是由于没有指定用户的认证db，可以通过--authenticationDatabase=database来指定

如上例可以改为

./mongoexport -h 172.128.111.56 --port 27017 -u admin -p admin --authenticationDatabase=admin -d test_db -c test_collection --type=csv -f uploadDate,length,filename -q '{"uploadDate":{"$lte":new Date("2019-12-02T16:00:00.000Z"),"$gt":new Date("2019-12-01T16:00:00.000Z")}}' -o /home/test/mongodb/test_20191202.csv