CODEWITHSUNDEEP
c#sql-servermongodb
SomeoneRandom
SomeoneRandom

Reputation: 264

MongoDB Select inside Update query

I am using a MongoDB database as a temp table for some import operations. I want to perform a stored procedure on the data before it is permanently persisted into our SQL database. This relies on using a map inside Mongo to replace 4 characters from a int field.

I am not quite sure how to do this in Mongo, but if these tables were inside SQL the query would look something like this:

UPDATE data SET number = 
IF LEN(number) >= 4 BEGIN
    CONCAT(SUBSTRING(number, 1, (LEN(number)-4)), (SELECT replacement FROM mapping WHERE original=SUBSTRING(number, (LEN(number)-4), 4))
END ELSE number

Meaning, if the number is 4 digits or more, replace the last 4 digits with the replacement in the mapping table, else do nothing.

Can anyone help me translate this into a MongoDB query(I am using C# in case that is relevant)? I am really lost right now.

Upvotes: 0

Views: 284

Answers (1)

Shad
Shad

Reputation: 4464

You can't do this work within one update query, but you can use JavaScript function for updating necessary items:

db.data.find().forEach(function (item) {
    var numberLength = item.number.length;
    if (numberLength >= 4) {
        var part1 = item.number.substring(0, numberLength - 4);
        var part2 = item.number.substring(numberLength - 4);

        var mapping = db.mapping.findOne({ original: part2 });

        item.number = part1 + (!!mapping ? mapping.replacement : "");
        db.data.save(item);
    }
});

Upvotes: 1

Related Questions