How to create field from substring of another field in MongoDb?

Question

I need to create a new field from a substring (two characters string) of another field in MongoDB. Help me, please.

Example:

field1  field2
abcdef  ab
qwerty  qw
opuytj  op

My code:

db.mycollection.update({}, {$set : {"field2": { $substr: [ {"field1"}, 0, 2 ] } }},
                        {upsert:false, multi:true})

score 3 · Accepted Answer · answered Dec 27 '19 at 05:56

3

if you have mongodb server v4.2 or newer you can do it easily like so:

db.mycollection.updateMany({}, [
    {
        "$set": {
            "field2": {
                "$substr": ["$field1", 0, 2]
            }
        }
    }
])

the above was generated using the following c# code:

using MongoDB.Entities;
using MongoDB.Entities.Core;

namespace StackOverflow
{
    public class Item : Entity
    {
        public string field1 { get; set; }
    }

    public class Program
    {
        private static void Main()
        {
            new DB("test", "localhost");

            (new[] {
              new  Item {field1 = "abcdef"},
              new  Item {field1 = "qwerty"},
              new  Item {field1 = "opuytj"}
            }).Save();

            DB.Update<Item>()
              .Match(_ => true)
              .WithPipelineStage("{$set : { field2 : { $substr: [ '$field1', 0, 2 ] } }}")
              .ExecutePipeline();
        }
    }
}

answered Dec 27 '19 at 05:56

Dĵ ΝιΓΞΗΛψΚ

5,068
3
13
26

In the Mongo shell i received: Error: the update operation document must contain atomic operators – Andre Vieira de Lima Dec 27 '19 at 13:35
is your mongo version 4.2 or above? more info [here](https://docs.mongodb.com/master/reference/command/update/index.html#update-with-aggregation-pipeline). – Dĵ ΝιΓΞΗΛψΚ Dec 27 '19 at 14:46
Yes. Version 4.2.2. – Andre Vieira de Lima Dec 27 '19 at 15:02
@AndreVieiradeLima in that case the above query should work. it works on my machine. make sure your mongo shell is also > 4.2 – Dĵ ΝιΓΞΗΛψΚ Dec 27 '19 at 15:29
For anyone else running into problems, remember the outer set of [ ] , which I overlooked – snapcrack Jan 05 '23 at 13:02

score 0 · Answer 2 · answered Dec 27 '19 at 05:46

0

Please use find with forEach keyword

db.demo.find({}).forEach( function(myDoc) { 
    var condname=myDoc.name;
    var condcat=myDoc.category;
    var replaceName =condcat.substring(0,2);

   db.demo.update({_id: myDoc._id}, {$set : {"name": replaceName }},
                        {upsert:false, multi:true}) 

} );

answered Dec 27 '19 at 05:46

Mahesh Bhatnagar

1,042
1
7
8

In the Mongo shell i received: TypeError: condname.substring is not a function – Andre Vieira de Lima Dec 27 '19 at 13:37

How to create field from substring of another field in MongoDb?

2 Answers2