I am trying to change the type of a field from within the mongo shell.
I am doing this...
db.meta.update[
{'fields.properties.default': { $type : 1 }},
{'fields.properties.default': { $type : 2 }}
]
But it's not working!
Xavier Guihot
47.3k21 gold badges264 silver badges168 bronze badges
asked Feb 11, 2011 at 19:18
1
The only way to change the $type
of the data is to perform an update on the data where the data has the correct type.
In this case, it looks like you're trying to change the $type
from
1 [double] to 2 [string].
So simply load the document from the DB, perform the cast [new String[x]
] and then save the document again.
If you need to do this programmatically and entirely from the shell, you can use the find[...].forEach[function[x] {}]
syntax.
In response to the second comment below. Change the field bad
from a number to a string in collection foo
.
db.foo.find[ { 'bad' : { $type : 1 } } ].forEach[ function [x] {
x.bad = new String[x.bad]; // convert field to string
db.foo.save[x];
}];
user6039980
2,8388 gold badges28 silver badges56 bronze badges
answered Feb 11, 2011 at 20:21
Gates VPGates VP
44.6k11 gold badges102 silver badges107 bronze badges
11
Convert String field to Integer:
db.db-name.find[{field-name: {$exists: true}}].forEach[function[obj] {
obj.field-name = new NumberInt[obj.field-name];
db.db-name.save[obj];
}];
Convert Integer field to String:
db.db-name.find[{field-name: {$exists: true}}].forEach[function[obj] {
obj.field-name = "" + obj.field-name;
db.db-name.save[obj];
}];
answered Oct 17, 2011 at 15:45
SimoneSimone
1,7391 gold badge10 silver badges2 bronze badges
7
Starting Mongo 4.2
,
db.collection.update[]
can accept an aggregation pipeline, finally allowing the update of a field based on its own value:
// { a: "45", b: "x" }
// { a: 53, b: "y" }
db.collection.updateMany[
{ a : { $type: 1 } },
[{ $set: { a: { $toString: "$a" } } }]
]
// { a: "45", b: "x" }
// { a: "53", b: "y" }
The first part
{ a : { $type: 1 } }
is the match query:- It filters which documents to update.
- In this case, since we want to convert
"a"
to string when its value is a double, this matches elements for which"a"
is of type1
[double]]. - This table provides the codes representing the different possible types.
The second part
[{ $set: { a: { $toString: "$a" } } }]
is the update aggregation pipeline:- Note the squared brackets signifying that this update query uses an aggregation pipeline.
$set
is a new aggregation operator [Mongo 4.2
] which in this case modifies a field.- This can be simply read as
"$set"
the value of"a"
to"$a"
converted"$toString"
. - What's really new here, is being able in
Mongo 4.2
to reference the document itself when updating it: the new value for"a"
is based on the existing value of"$a"
. - Also note
"$toString"
which is a new aggregation operator introduced inMongo 4.0
.
In case your cast isn't from double to string, you have the choice between different conversion operators introduced in Mongo 4.0
such as
$toBool
, $toInt
, ...
And if there isn't a dedicated converter for your targeted type, you can replace { $toString: "$a" }
with a $convert
operation: { $convert: { input: "$a", to: 2 } }
where the value for to
can be found in this
table:
db.collection.updateMany[
{ a : { $type: 1 } },
[{ $set: { a: { $convert: { input: "$a", to: 2 } } } }]
]
answered Jun 12, 2019 at 20:43
Xavier GuihotXavier Guihot
47.3k21 gold badges264 silver badges168 bronze badges
3
For string to int conversion.
db.my_collection.find[].forEach[ function[obj] {
obj.my_value= new NumberInt[obj.my_value];
db.my_collection.save[obj];
}];
For string to double conversion.
obj.my_value= parseInt[obj.my_value, 10];
For float:
obj.my_value= parseFloat[obj.my_value];
answered Mar 17, 2012 at 0:35
David DehghanDavid Dehghan
19.6k8 gold badges100 silver badges90 bronze badges
3
db.coll.find[].forEach[function[data] {
db.coll.update[{_id:data._id},{$set:{myfield:parseInt[data.myfield]}}];
}]
answered Apr 12, 2012 at 13:01
RussellRussell
2693 silver badges2 bronze badges
0
all answers so far use some version of forEach, iterating over all collection elements client-side.
However, you could use MongoDB's server-side processing by using aggregate pipeline and $out stage as :
the $out stage atomically replaces the existing collection with the new results collection.
example:
db.documents.aggregate[[
{
$project: {
_id: 1,
numberField: { $substr: ['$numberField', 0, -1] },
otherField: 1,
differentField: 1,
anotherfield: 1,
needolistAllFieldsHere: 1
},
},
{
$out: 'documents',
},
]];
answered Sep 3, 2018 at 12:39
user3616725user3616725
3,2731 gold badge16 silver badges26 bronze badges
1
To convert a field of string type to date field, you would need to iterate the cursor returned by the find[]
method using the
forEach[]
method, within the loop convert the field to a Date object and then update the field using the $set
operator.
Take advantage of using the Bulk API for bulk updates which offer better performance as you will be sending the operations to the server in batches of say 1000 which gives you a better performance as you are not sending every request to the server, just once in every 1000 requests.
The following demonstrates this approach, the first example uses the Bulk API available in MongoDB versions
>= 2.6 and < 3.2
. It updates all the documents in the collection by changing all the created_at
fields to date fields:
var bulk = db.collection.initializeUnorderedBulkOp[],
counter = 0;
db.collection.find[{"created_at": {"$exists": true, "$type": 2 }}].forEach[function [doc] {
var newDate = new Date[doc.created_at];
bulk.find[{ "_id": doc._id }].updateOne[{
"$set": { "created_at": newDate}
}];
counter++;
if [counter % 1000 == 0] {
bulk.execute[]; // Execute per 1000 operations and re-initialize every 1000 update statements
bulk = db.collection.initializeUnorderedBulkOp[];
}
}]
// Clean up remaining operations in queue
if [counter % 1000 != 0] { bulk.execute[]; }
The next example applies to the new MongoDB version 3.2
which has since deprecated the Bulk API and provided a newer set of apis using
bulkWrite[]
:
var bulkOps = [];
db.collection.find[{"created_at": {"$exists": true, "$type": 2 }}].forEach[function [doc] {
var newDate = new Date[doc.created_at];
bulkOps.push[
{
"updateOne": {
"filter": { "_id": doc._id } ,
"update": { "$set": { "created_at": newDate } }
}
}
];
}]
db.collection.bulkWrite[bulkOps, { "ordered": true }];
answered Jan 22, 2016 at 11:44
chridamchridam
97k21 gold badges223 silver badges226 bronze badges
1
To convert int32 to string in mongo without creating an array just add "" to your number :-]
db.foo.find[ { 'mynum' : { $type : 16 } } ].forEach[ function [x] {
x.mynum = x.mynum + ""; // convert int32 to string
db.foo.save[x];
}];
answered Oct 3, 2014 at 14:19
Giulio RoggeroGiulio Roggero
1,6621 gold badge16 silver badges11 bronze badges
What really helped me to change the type of the object in MondoDB was just this simple line, perhaps mentioned before here...:
db.Users.find[{age: {$exists: true}}].forEach[function[obj] {
obj.age = new NumberInt[obj.age];
db.Users.save[obj];
}];
Users are my collection and age is the object which had a string instead of an integer [int32].
answered Mar 28, 2017 at 5:46
FelipeFelipe
10110 bronze badges
0
You can easily convert the string data type to numerical data type.
Don't forget to change collectionName & FieldName.
for ex : CollectionNmae : Users & FieldName : Contactno.
Try this query..
db.collectionName.find[].forEach[ function [x] {
x.FieldName = parseInt[x.FieldName];
db.collectionName.save[x];
}];
answered Jan 25, 2017 at 13:40
I need to change datatype of multiple fields in the collection, so I used the following to make multiple data type changes in the collection of documents. Answer to an old question but may be helpful for others.
db.mycoll.find[].forEach[function[obj] {
if [obj.hasOwnProperty['phone']] {
obj.phone = "" + obj.phone; // int or longint to string
}
if [obj.hasOwnProperty['field-name']] {
obj.field-name = new NumberInt[obj.field-name]; //string to integer
}
if [obj.hasOwnProperty['cdate']] {
obj.cdate = new ISODate[obj.cdate]; //string to Date
}
db.mycoll.save[obj];
}];
answered Jan 20, 2016 at 9:55
AakashAakash
2312 silver badges13 bronze badges
demo change type of field mid from string to mongo objectId using mongoose
Post.find[{}, {mid: 1,_id:1}].exec[function [err, doc] {
doc.map[[item, key] => {
Post.findByIdAndUpdate[{_id:item._id},{$set:{mid: mongoose.Types.ObjectId[item.mid]}}].exec[[err,res]=>{
if[err] throw err;
reply[res];
}];
}];
}];
Mongo ObjectId is just another example of such styles as
Number, string, boolean that hope the answer will help someone else.
Pang
9,192146 gold badges85 silver badges118 bronze badges
answered Aug 26, 2017 at 3:40
I use this script in mongodb console for string to float conversions...
db.documents.find[{ 'fwtweaeeba' : {$exists : true}}].forEach[ function[obj] {
obj.fwtweaeeba = parseFloat[ obj.fwtweaeeba ];
db.documents.save[obj]; } ];
db.documents.find[{ 'versions.0.content.fwtweaeeba' : {$exists : true}}].forEach[ function[obj] {
obj.versions[0].content.fwtweaeeba = parseFloat[ obj.versions[0].content.fwtweaeeba ];
db.documents.save[obj]; } ];
db.documents.find[{ 'versions.1.content.fwtweaeeba' : {$exists : true}}].forEach[ function[obj] {
obj.versions[1].content.fwtweaeeba = parseFloat[ obj.versions[1].content.fwtweaeeba ];
db.documents.save[obj]; } ];
db.documents.find[{ 'versions.2.content.fwtweaeeba' : {$exists : true}}].forEach[ function[obj] {
obj.versions[2].content.fwtweaeeba = parseFloat[ obj.versions[2].content.fwtweaeeba ];
db.documents.save[obj]; } ];
And this one in php]]]
foreach[$db->documents->find[array["type" => "chair"]] as $document]{
$db->documents->update[
array['_id' => $document[_id]],
array[
'$set' => array[
'versions.0.content.axdducvoxb' => [float]$document['versions'][0]['content']['axdducvoxb'],
'versions.1.content.axdducvoxb' => [float]$document['versions'][1]['content']['axdducvoxb'],
'versions.2.content.axdducvoxb' => [float]$document['versions'][2]['content']['axdducvoxb'],
'axdducvoxb' => [float]$document['axdducvoxb']
]
],
array['$multi' => true]
];
}
answered Apr 3, 2014 at 9:25