MongoDB concatenate strings from two fields into a third field

Question

How do I concatenate values from two string fields and put it into a third one?

I've tried this:

db.collection.update(
  { "_id": { $exists: true } },
  { $set: { column_2: { $add: ['$column_4', '$column_3'] } } },
  false, true
)

which doesn't seem to work though, and throws not ok for storage.

I've also tried this:

db.collection.update(
  { "_id": { $exists : true } },
  { $set: { column_2: { $add: ['a', 'b'] } } },
  false, true
)

but even this shows the same error not ok for storage.

I want to concatenate only on the mongo server and not in my application.

see <stackoverflow.com/questions/4110897/mongodb-concatenate-results>? — Vinit Prajapati
– Vinit Prajapati, Commented Oct 10, 2012 at 13:36
forEach is inefficient, multiple update is what I'm looking for. Also it doesn't explain, how to update the value of another field with the concatenated string, this is what I'm looking for col3 = col1 + col2 "+" implying concatenation — Yogesh Mangaj
– Yogesh Mangaj, Commented Oct 10, 2012 at 14:06
Mongodb does not, atm, allow the relfection of it's own document fields within the query without using JS functions. I would strongly advise not using the JS functions since they have been proven to make a query 10x slower and also have other problems. You will need to find some creative way of solving this — Sammaye
– Sammaye, Commented Oct 10, 2012 at 14:22
As to normal string concatenation you dont need a $add op since it is isn't like SQL, instead you can just do column_2:'a'+'b' — Sammaye
– Sammaye, Commented Oct 10, 2012 at 14:24

Xavier Guihot · Accepted Answer · 2020-03-29 11:50:58Z

34

You can use aggregation operators $project and $concat:

db.collection.aggregate([
  { $project: { newfield: { $concat: [ "$field1", " - ", "$field2" ] } } }
])

edited Mar 29, 2020 at 11:50

Xavier Guihot

62.8k26 gold badges320 silver badges202 bronze badges

answered Jan 21, 2016 at 21:24

rebe100x

1,49315 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jamby Over a year ago

The correct answer would include $out as the last pipeline stage, to output in the same collection (or a new one), since OP wants to update the collection

Kenigmatic Over a year ago

MongoDB 4.2 now has a $merge pipeline stage which has some advantages over $out, depending on your performance, concurrency and consistency requirements.

OJT Over a year ago

What would the above aggregation look like with a step that updates the existing document with the new concatenated field?

William Z · Accepted Answer · 2012-10-10 21:48:59Z

10

Unfortunately, MongoDB currently does not allow you to reference the existing value of any field when performing an update(). There is an existing Jira ticket to add this functionality: see SERVER-1765 for details.

At present, you must do an initial query in order to determine the existing values, and do the string manipulation in the client. I wish I had a better answer for you.

answered Oct 10, 2012 at 21:48

William Z

11.2k4 gold badges33 silver badges25 bronze badges

1 Comment

Kenigmatic Over a year ago

FYI (7 years later) MongoDB has multiple ways to accomplish this without client-side code. This was the best answer in 2012, but William Z's wish for a better answer has been granted. :)

s7vr · Accepted Answer · 2019-10-10 21:55:21Z

7

You could use $set like this in 4.2 which supports aggregation pipeline in update.

db.collection.update(
   {"_id" :{"$exists":true}},
   [{"$set":{"column_2":{"$concat":["$column_4","$column_3"]}}}]
)

edited Oct 10, 2019 at 21:55

answered Oct 4, 2019 at 1:10

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Comments

Kenigmatic · Accepted Answer · 2019-10-10 21:35:36Z

Building on the answer from @rebe100x, as suggested by @Jamby ...

You can use $project, $concat and $out (or $merge) in an aggregation pipeline. https://docs.mongodb.org/v3.0/reference/operator/aggregation/project/ https://docs.mongodb.org/manual/reference/operator/aggregation/concat/ https://docs.mongodb.com/manual/reference/operator/aggregation/out/

For example:

    db.collection.aggregate(
       [
          { $project: { newfield: { $concat: [ "$field1", " - ", "$field2" ] } } },
          { $out: "collection" }
       ]
    )

With MongoDB 4.2 . . .

MongoDB 4.2 adds the $merge pipeline stage which offers selective replacement of documents within the collection, while $out would replace the entire collection. You also have the option of merging instead of replacing the target document.

    db.collection.aggregate(
       [
          { $project: { newfield: { $concat: [ "$field1", " - ", "$field2" ] } } },
          { $merge: { into: "collection", on: "_id", whenMatched: "merge", whenNotMatched: "discard" }
       ]
    )

You should consider the trade-offs between performance, concurrency and consistency, when choosing between $merge and $out, since $out will atomically perform the collection replacement via a temporary collection and renaming.

https://docs.mongodb.com/manual/reference/operator/aggregation/merge/ https://docs.mongodb.com/manual/reference/operator/aggregation/merge/#merge-out-comparison

Hanoj B · Accepted Answer · 2020-09-03 15:30:48Z

2

**

in my case this $concat worked for me ...

**

db.collection.update( { "_id" : {"$exists":true} }, 
   [ { 
       "$set" : { 
                 "column_2" :  { "$concat" : ["$column_4","$column_3"] }
                }
      }
   ]

edited Sep 3, 2020 at 15:30

answered Jan 29, 2020 at 4:48

Hanoj B

3712 silver badges14 bronze badges

Comments

Adam Bardon · Accepted Answer · 2018-02-28 11:31:26Z

let suppose that you have a collection name is "myData" where you have data like this

{
"_id":"xvradt5gtg",
"first_name":"nizam",
"last_name":"khan",
"address":"H-148, Near Hero Show Room, Shahjahanpur",
}

and you want concatenate fields (first_name+ last_name +address) and save it into "address" field like this

{
"_id":"xvradt5gtg",
"first_name":"nizam",
"last_name":"khan",
"address":"nizam khan,H-148, Near Hero Show Room, Shahjahanpur",
}

now write query will be

{
var x=db.myData.find({_id:"xvradt5gtg"});
x.forEach(function(d)
    { 
        var first_name=d.first_name;
        var last_name=d.last_name;
        var _add=d.address;  
        var fullAddress=first_name+","+last_name+","+_add; 
        //you can print also
        print(fullAddress); 
        //update 
        db.myData.update({_id:d._id},{$set:{address:fullAddress}});
    })
}

Kenigmatic · Accepted Answer · 2019-10-04 00:54:05Z

1

You can also follow the below.

db.collectionName.find({}).forEach(function(row) { 
    row.newField = row.field1 + "-" + row.field2
    db.collectionName.save(row);
});

edited Oct 4, 2019 at 0:54

Kenigmatic

4467 silver badges16 bronze badges

answered Sep 18, 2017 at 3:13

ayyappa maddi

9713 gold badges11 silver badges18 bronze badges

Comments

Sahil Thummar · Accepted Answer · 2022-04-22 14:33:22Z

0

Find and Update Each Using For Loop

Try This:

db.getCollection('users').find({ }).forEach( function(user) {
    user.full_name = user.first_name + " " + user.last_name;
    db.getCollection('users').save(user);
});

Or Try This:

db.getCollection('users').find({ }).forEach( function(user) {
    db.getCollection('users').update(
        { _id: user._id },
        { $set: { "full_name": user.first_name + " " + user.last_name } }
    )
});

answered Apr 22, 2022 at 14:33

Sahil Thummar

2,61622 silver badges21 bronze badges

Collectives™ on Stack Overflow

MongoDB concatenate strings from two fields into a third field

8 Answers 8

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related