Can you update a collection in MongoDB and remove the first/last char on one field?

Question

My question might be simple be here's some more context to it. I have a MySQL DB, I've used an ETL tool to populate a MongoDBwith, however I couldn't manage to create proper ObjectId reference to it (I can only get a string of the ObjectId.

So far I've had an idea (maybe crazy but still.. could work)

I got this field populated like this in one document :

"field1" : "ObjectId('5d48845c456145ee9d1ccffde')",

What I would want to achieve through mongoDB is removing the first and last char to get (stripping the double quotes):

"field1" : ObjectId('5d48845c456145ee9d1ccffde'),

(note that MongoDB seems to automatically convert simple to Double quote after the change, so my reference become corret).

Problem is, I don't find anything close to a sort of Update script for MongoDB to achieve this.

Is there any way to do this ?

Using NodeJS could work, however, querying the document at this state doesn't return the field1 (probably cause it find it incorect)...

Stating the obvious was no help in here. – nathan bersez Aug 09 '19 at 12:10 — nathan bersez, Aug 09 '19 at 12:10

score 2 · Accepted Answer · answered Aug 06 '19 at 17:25

If its one time update, you can use the following query:

db.COLLECTION.aggregate([
    {
        $addFields:{
            "field1":{
                $toObjectId:{
                    $substrBytes:[
                        "$field1",
                        10,
                        24
                    ]
                }
            }
        }
    },
    {
        $out:"COLLECTION"
    }   
])

In aggregation, the 'field1' is cast to ObjectId. Later on, the old data in the collection is replaced with the aggregated one.

Thanks a lot, that was exactly what I was looking for! – nathan bersez Aug 08 '19 at 10:37 — nathan bersez, Aug 08 '19 at 10:37

Can you update a collection in MongoDB and remove the first/last char on one field?

1 Answers1