How to use the substr function in mongodb?

Question

This is my solution to export data from mongo into a table into oracle, if there's a better way, I'd appreciate that as well.

My current query is:

db.getCollection('table_name').find({"key.specificKey": "value"})

What I want to do is select a specific substring to display. I tried using the substr function as found in the mongodb docs. However this wasn't working for me.

db.getCollection('table_name').aggregate({columnName: {$substr: ["$key", 0, 2]}})

I also tried using the match function as suggested here. But that was also not working.

db.getCollection('table_name').aggregate($match: {"key.specificKey": "value"}, {columnName: {$substr: ["$key", 0, 2]}})

Can someone correct my syntax to do this? I'm using robomongo, if that matters.

Sample data:

{
    "_id" : ObjectId("hey"),
    "key" : {
        "keyId" : NumberLong(1234),
        "keyName" : "valueName",
    }
}

Answer 1

You need to use the $project stage in the aggregation pipeline for this to work, as follows:

db.getCollection('table_name').aggregate([{
    $match: {
        "key.specificKey": "value"
    }
}, {
    $project: {
        columnName: {
            $substr: ["$key", 0, 2]
        }
    }
}])