Why does MongoDB produce invalid JSON? The ObjectId is not quoted, breaks jq parser

Question

I've searched the forum and seen many folks with a similar problem, but not this exact one.

I think my question is the simplest form, and there must be something I'm missing because no one is asking it.

I have a shell script that calls a MongoDB script and gets the results in a file. I then want to parse that file with jq.

jq is breaking because the output from the query is not valid JSON. The offender is the ObjectId. I'm at a total loss as to how something that's "ALL JSON ALL THE TIME" produces invalid JSON.

I'm pretty sure there's something fundamental that I'm missing.

I have a file called MyMongoScript.js. Its contents look like this:

db.WorkflowJobs.find().sort({"STATUS":1}).forEach(printjson)

I call MyMongScript.js with the following command:

mongo -u $MONGO_U -p $MONGO_P $MONGO_DB -quiet --eval "var DATE_TO_RUN=\"$DATE_TO_RUN\"" MyMongoScript.js  

Here's the results to STDOUT:

{
"_id" : ObjectId("52816fd50bc9efc3e6d8e33f"),
"WORK_TYPE" : "HIVE",
"Script_Name" : "upload_metrics_LANDING_to_HIST.sql",
"Stop_On_Fail" : true,
"STATUS" : "READY",
"START_TS" : "NULL",
"END_TS" : "NULL",
"DURATION" : "NULL",
"INS_TS" : "Mon Nov 11 2013 16:01:25 GMT-0800 (PST)"
}

Here's what jsonlint.com says about it:

Parse error on line 2:
{    "_id": ObjectId("52816fd50b
------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '['

Any help much appreciated.

Answer 1

Try this for your MyMongoScript.js:

db.WorkflowJobs.find().sort({"STATUS":1}).forEach(function(myDoc){myDoc._id=myDoc._id.valueOf();print(tojson(myDoc))});

The key is valueOf() which will set your ObjectId to a String.

EDITED Left out a paren.