Getting "Pretty" data from MongoDB JSON object using

Question

Update
After trying out Saravana's solution, I am still getting a weird syntax error. After going through it for an hour I can't figure out why... (also sorry for the bad copy paste.

db.submissions.aggregate(  
    [  
        {$match: { started: {'$gte': Date('2018-01-02 01:01:01.001'), '$lte': 
Date('2018-01-02 13:15:59.999' )}}}  
        {$project: { _id: 0,   
            data: [   
                {$dateToString: { format: "%Y-%m-%dT%H:%M:%S", date: "$started" } },  
                {$dateToString: { format: "%Y-%m-%dT%H:%M:%S", date: "$finished" } },  
                '$size'  
               ]   
            }  
        }  
    ]  
)

Syntax error on line 4 missing ] at position 8 (but that is before any code, it makes no sense)

Original Post
I am new to both mongo and JS, so please bear with my "basic" code. I am running a query.js, and I get the data I want, I just need to clean it up to look like a csv or something similar... Basically my query returns 2 dates and a file size. (example at bottom)

var cursor=db.submissions.find({started : {'$gte': ISODate('2018-01-02 
01:01:01.001'), '$lte': ISODate('2018-01-02 13:15:59.999' )}},{started : 1, 
finished : 1, "inputs.inputFile.size" : 1})

cursor.forEach(printjson);

This returns something like this...

"_id" : ObjectId("5a4b7fbe77b5260001843b82"),
"started" : ISODate("2018-01-02T12:49:03.745Z"),
"finished" : ISODate("2018-01-02T12:49:05.695Z"),
"size" : 4560

"_id" : ObjectId("5a4b85be2ea4170001707562"),
"started" : ISODate("2018-01-02T13:14:39.851Z"),
"finished" : ISODate("2018-01-02T13:14:44.363Z"),
"size" : 4547

I only want the data (not field names) returned to look like [2018-01-02T12:49:03.745Z, 2018-01-02T12:49:05.695Z, 4560] so I can create a csv. Anyway I am at a complete loss, any help would be appreciated

Saravana · Accepted Answer · 2018-01-24 07:01:15Z

1

you can use aggregation pipeline to format the data

db.col.aggregate(
    [
        {$match : ...}, // your filters
        {$project : { 
            _id :0, 
            data : [ 
                {$dateToString : { date : "$started", format : "%Y-%m-%dT%H:%M:%S" } },
                {$dateToString : { date : "$finished", format : "%Y-%m-%dT%H:%M:%S" } },
                "$size" 
               ] 
            }
        }
    ]
)

result

{ "data" : [ "2018-01-02T12:49:03", "2018-01-02T12:49:05", 4560 ] }
{ "data" : [ "2018-01-02T13:14:39", "2018-01-02T13:14:44", 4547 ] }

iterate the cursor to get the values only

.forEach(function(doc) {print(doc.data)})

result

2018-01-02T12:49:03,2018-01-02T12:49:05,4560
2018-01-02T13:14:39,2018-01-02T13:14:44,4547

edited Jan 24, 2018 at 7:01

answered Jan 24, 2018 at 3:48

Saravana

12.8k2 gold badges42 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Saravana Over a year ago

data : [                  {$dateToString : { date : "$started", format : "%Y-%m-%dT%H:%M:%S" } },                 {$dateToString : { date : "$finished", format : "%Y-%m-%dT%H:%M:%S" } },                 "$size"                 ]

can you try without separating array elements to new line

TJ Snow Over a year ago

Same error. Says [thread1] SyntaxError: missing ] after element list @query.js:4:8

Saravana Over a year ago

we are missing a , after $match stage, updated answer, please check

TJ Snow Over a year ago

That stopped the errors! You are amazing thank you so much. One last question, where do I put the .forEach(function(doc) {print(doc.data)}) I added it at the bottom of the script and it does nothing.

Saravana Over a year ago

the aggregation pipeline returns a cursor, we need to forEach on the cursor

Collectives™ on Stack Overflow

Getting "Pretty" data from MongoDB JSON object using

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related