CODEWITHSUNDEEP
mysqlmongodbcsvmongo-shell
Grynets
Grynets

Reputation: 2525

MongoDB type update from NumberLong to String

I've imported CSV file to my mongodb. CSV have separators as needed to mongo and was received from MySQL database with this query:
SELECT * FROM csgo_users INTO OUTFILE 'b1.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Also I've tried to receive CSV from Sequel Pro with it's export functions.
In my csv I have field with 17 characters length. In MySQL database this field has type VARCHAR but contains 17-digits number.
After importing csv to mongo I got this field with type NumberLong, but I want it to be string.
I've tried so far to:

or this: 

db.csgo_users.find({"steam_id": {$type: 18}})
  .toArray()
  .map(function(v){
     v.steam_id = new String(v.toSring());
     db.csgo_users.save(v)
  })


- I've tried a lot of solutions with forEach() like this or this or this etc.


For the last one example of my tries I've got not string, but Object, {"N",u","m","b","e","r","L","o","n","g"..} but I want it to be "123456789", not Object.
I'm using MongoDB 3.4 docs.
So, my question is, how to change "field" type from NumberLong to String?

Upvotes: 7

Views: 11378

Answers (2)

Rodrigo Polo
Rodrigo Polo

Reputation: 4774

The type NumberLong exists for a reason, to handle huge numbers, similar to BigInt on SQL. MongoDB relies on JavaScript which have at most 53 bits for integers, in ES6 the largest exact integral value is 253-1, or 9007199254740991, for this reason, "converting" from NumberLong to a simple string isn't as simple as the previous answer, here is an example:

var huge = NumberLong("987654321987654321");
huge.valueOf(); // 987654321987654300
huge.toString(); // NumberLong("987654321987654321")
huge.valueOf().toString(); // 987654321987654300

With this example, it is clear that JavaScript is rounding up the numbers while using valueOf(), and lacking any sane response and documentation, I came with a workaround for this situation, using RegEx to remove any non numerical characters from the toString() function:

huge.toString().replace(/[^\d]/g, '') // 987654321987654321

It isn't pretty, but it works, any other better solution is always appreciated. As a bonus fact, using JSON.stringify converts the value into an object using the special char $ to represent the function to call while processing the value, the way MongoDB handles this issues while dealing with common day to day JSON objects:

JSON.stringify(huge) // {"$numberLong":"987654321987654321"}

Update: The right way of converting data with MongoDB is with aggregations using a projection:

db.getCollection('mycollection').aggregate([
    {$match: {
        /* your match query object */
    }},
    {$project: {
        _id: 0, // to ignore the document id
        myStringObj: {$toString: '$myNumberLongObj'} // from NumberLong to String
    }},
], {allowDiskUse: true});

Upvotes: 7

Julien TASSIN
Julien TASSIN

Reputation: 5212

You can use valueOf() to get the value of a NumberLong as a javascript number value.

Casting NumberLong to Number :

NumberLong('5').valueOf() // 5

Then, you can then use easilly toString() on your number to get the String value.

Casting NumberLong to String :

NumberLong('5').valueOf().toString() // "5"

Upvotes: 14

Related Questions