Search code examples
mongodbmongodb-querymongodumpmongoexport

how to store mongodb distinct or aggregation result to a file

I am novice to mongodb views. I have a distinct result of mobile numbers, I want to store them in csv file.

Below is my query which is working fine:

db.myCollection.distinct('jsonObject.keyIdentifier',{'$and' : [{'jsonObject.responseRaw' : {$not: /<Amount>/}},{'jsonObject.createDate':{$gt: '2020-05-12'} }]})

output: on shell/console

["9898989896",
"9898989897",
"9898989898",
...,
...
]

I read some where that I can not just export this result to flat file. I need to create a view first. Then I need to export that view to a file. But I am not able to generate a view.. I tried following

db.createView("jsonObject.keyIdentifier","myCollection", [{ $group: { _id: "'$and' : [{'jsonObject.responseRaw' : {$not: /<Amount>/}},{'jsonObject.createDate':{$gt: '2020-05-12'} }]"}}]);

Then I write export query:

mongoexport -d MongoDB -c jsonObject.keyIdentifier --type=csv -f "_id" -o D:\uploads\DAta\csvFile\report111.csv

but instead I got below text in csv file

_id
"'$and' : [{'jsonObject.responseRaw' : {$not: /<Amount>/}},{'jsonObject.createDate':{$gt: '2020-05-01'} }]"

How to resolve this dilemma ... how can I get the output store to a file.

Solution

  • Ok, what worked for me here is $out stage of aggregate method of mongodb. What I did is store a aggregate result to a new collection through $out(it will be made automatically) and then export it to csv by using mongoexport statement. Below are the steps:

    1. 

    db.myCollection.aggregate([{'$match':{'$and' : [{'jsonObject.responseRaw' : {'$not': /<Amount>/}},{'jsonObject.createDate':{'$gt': '2020-05-01'}}]}},{'$group':{'_id': '$jsonObject.keyIdentifier'}},{$out: "collectionB"}])

    2. 

    mongoexport -d MyMongoDb -c collectionB  -f _id --csv > D:\uploads\DAta\csvFile\results1111.csv

    note: here -f is the indicating fields; necessary while creating csv. I needed only _id.