How to return raw JSON directly from a mongodb query?-CodePudding

In MongoDB you can query documents, for example using db.mycollection.find({"something":true}) and get the following result:

{
"someDate": ISODate("2022-10-24T17:21:44.980Z"),
"something": true,
"hello": "world"
}

This result, however, is not valid JSON (Due to ISODate). How can I change the query above to make MongoDB return raw/valid JSON?

Btw, I'm sure this question must have been asked before, but I cannot find it :(

CodePudding user response：

There are a number of existing answers, I'll clarify a few:

Use aggregate to produce the output in JSON format: Playground

db.collection.aggregate([
  {
    $match: {
      something: true
    }
  },
  {
    $project: {
      _id: 1,
      someDate: {
        $dateToString: {
          format: "%Y-%m-%dT%H:%M:%S:%LZ",
          date: "$someDate"
        }
      },
      something: 1,
      hello: 1
    }
  }
])

Loop through your query in your application: (example, node.js)

db.mycollection.find({"something":true}).forEach(function(doc) {   
   doc.someDate = doc.someDate.toISOString() // or even .toJSON()
})

// Or with await

const records = await db.mycollection.find({"something":true}).map(doc => {
  doc.someDate = doc.someDate.toISOString()
  return doc
}).toArray()

CodePudding user response：

The details of where you are running this command are very important, can you please share those?

I am guessing that you are probably running this via the (older) mongo utility (as opposed to the newer mongosh). But confirmation of that, and the database version that you are using, would both be helpful. I will retain this assumption for the purposes of this answer.

This result, however, is not valid JSON (Due to ISODate).

The database itself doesn't return some text that has ISODate. In fact, it doesn't return or "speak" JSON at all. Rather, the database communicates via "Binary JSON" or BSON for short. In fact, the "Does MongoDB use BSON or JSON?" section of this page specifically mentions the following:

Firstly, BSON documents may contain Date or Binary objects that are not natively representable in pure JSON.

So when you see things like ISODate() that is the client application wrapping and representing the rich BSON document in a more limited (and text-based) JSON-like form. Importantly, this is often for readability purposes. You should be able to natively pass around and operate on information (documents) returned by the database directly in the application without doing any sort of transformation and without losing rich type information. Additional reading material about BSON is here.

Getting back to the original question, if you want to have the shell print out a valid JSON document than you can do that via additional helpers. In the older mongo utility, a reproduction of the situation described in the question is:

> db.mycollection.findOne({"something": true})
{
    "_id" : 1,
    "someDate" : ISODate("2022-11-30T14:38:37.711Z"),
    "something" : true,
    "hello" : "world"
}

The shell itself can understand and operate on ISODate() (and other functions of that nature). If you did want to remove things like ISODate() for some reason, then you can leverage the JSON.stringify() functionality (reformatted with line indents for readability):

> JSON.stringify( db.mycollection.findOne({"something": true}) )
{
  "_id":1,
  "someDate":"2022-11-30T14:38:37.711Z",
  "something":true,
  "hello":"world"
}

The newer mongosh shell offers even more utility here:

> EJSON.serialize( db.mycollection.findOne() )
{
  _id: 1,
  someDate: { '$date': '2022-11-30T14:38:37.711Z' },
  something: true,
  hello: 'world'
}

Via these EJSON functions, mongosh is attempting to preserve type information when it prints the data in this format. Notice that in the earlier example the date was just represented as a string, but here the shell is using Extended JSON to capture the fact that the type of someDate is a Date.