Does anyone have experience in working with MongoDB and CF. We have an application that uses Java to access an older version of MongoDB - version 3.4. So far I have not had any problems getting the data from the returned array of structures, Unfortunately when I try and convert this to JSON, it crashes the CF Service, requiring a restart. I’ve narrowed it down to the _id BSON object that’s returned as part of the array. Apparently it doesn’t get along too well with ColdFusion. HoweverI have been having a lot of trouble trying to retrieve value of the the _id field. Using more recent versions, you can use $convert or similar to convert the value to a string etc. However these methods are not available in the version of MongoDB I’m using. Here’s s code sample:

public any function getSeasons() output="false" {
    var  db = getmongoservice("mongo_datasource").db("theDatabase");
    var theSeasons = db.seasons.aggregate( [
            { $match: { seasonStatus: "Active" }  },
            { $project: {
                "_id": 1, // 1: include this field, 0 exclude it, or convert field to another datatype
                "seasonName" : "$name",
                "seasonStartDateTime": { $dateToString: { format: "%m/%d/%Y", date: "$startDate" } } ,
                "seasonEndDateTime":  { $dateToString: { format: "%m/%d/%Y", date: "$endDate" } },
                "materials":1 // this will include all the elements that make up the embedded documents.
            }
        }
    ] );
    // this returns an array of structs with materials being a further array of structs.
    seasonsArray = theSeasons.toArray();
    return seasonsArray;
}

Generally pretty simple stuff. it returns an array of structs, with the _id field as an object and the Materials field as an array of structs, with each array item having an id field that’s a BSON object. So the question I have does anyone know how I can retrieve the value of the _id fields only and not their respective BSON objects? Many thanks.

Without seeing an example of the output it's hard to give a lot useful input with that in mind, you can always treat the output as string and regEx replace invalid elements to make it a valid JSON string manually.

Here’s an example of the output.

That looks pretty standard on quick look, ColdFusion Fiddle I suspect the data might be large and timing out, or another error just not getting caought???

In the end the issue turned out to be a combination of an antique version of MongoDB (v3.4 vs currently v 8.3), and how serializeJSON parses things. The _id field for instance was a BSON object. Unfortunately SerializeJSON cannot handle BSON objects. So far it looks like the solution is to upgrade to a more recent version of MongoDB (to take advantages of language improvements) and to use a different JSON serializer. When I tried serializing the same array of structs with embedded BSON objects using a different engine, that worked.

Cool, glad you got it sorted!

Mostly we’re still having issues, but at least they’re far more understandable. I now understand why companies like CData can make a profit selling drivers for MongoDB that let you use standard ANSI SQL. https://www.cdata.com/kb/tech/mongodb-jdbc-coldfusion-query.rst