Shulou(Shulou.com)06/01 Report--

Recently received a development request, due to business adjustment, need the assistance of DBA to replace a collection in the MongoDB database. For example, we need to replace the character "beautiful" in the B field of set A with "very beautiful". Personally, I feel that if this requirement is dealt with on SQL Server or MySQL database, it is a piece of cake, and if it is for MongoDB data, it may be troublesome.

1. Replacement functions in Common Relational data databases

In the SQL Server database, we use the Replace function to replace characters.

Grammar

REPLACE (''string_replace1'',' 'string_replace2'',' 'string_replace3'')

Parameters.

'' string_replace1''

The string expression to be searched. String_replace1 can be character data or binary data.

'' string_replace2''

The string expression to be found. String_replace2 can be character data or binary data.

'' string_replace3''

The string expression used in substitution. String_replace3 can be character data or binary data.

For example, replace SQL Server in the string SQL Server database with MongoDB.

The implementation statement is as follows:

The syntax is similar in the MySQL database.

two。 Implementation of replacement method in MongoDB

Unfortunately, we haven't found a replacement function like SQL Server so far, so how do we replace the data in the document? Especially when the number of documents in the collection is large.

Suppose you now have a user information document, as follows. Note that there is a contact address in it.

{"_ id": ObjectId ("5c77e5f85f53771b74bc87e0"), "username": "hanmeimei", "email": "123456@126.com", "tel": 7891233, "addresses": {"provice": "Shandong Province", "city": "Caoji Town, Juancheng County, Heze City", "stree": "28 Wuzhuangyuan Street"}} / * 2 createdAt:2019/2/28 9 pm 9 ObjectId 45 5c77e5f85f53771b74bc87df 28 / {"_ id": ObjectId ("5c77e5f85f53771b74bc87df"), "username": "Lily", "email": "123456@126.com", "tel": 123455, "addresses": [{"provice": "Henan Province", "city": "Liji Town, Dengfeng County, Zhengzhou City" "stree": "28 Shaolin Street"}]}, / * 3 createdAt:2019/2/28 9 p.m. 45 ObjectId 28mm / {"_ id": ObjectId ("5c77e5f85f53771b74bc87de"), "username": "Lily", "email": "123456@126.com", "tel": 123455, "addresses": {"provice": "Shandong Province" "city": "Caoji Town, Dingtao County, Heze City", "stree": "28 Wuzhuangyuan Street"}, / * 4 createdAt:2019/2/28 9 createdAt:2019/2/28 9 id 4515 28pm / {"_ id": ObjectId ("5c77e5f85f53771b74bc87dd"), "username": "jack", "email": "123456@126.com", "tel": 123455, "addresses": {"provice": "Shandong Province" "city": "Dingtao County, Heze City", "stree": "28 Wuzhuangyuan Street"}}

Now, because of the administrative division, Dingtao County of Heze City is divided into Dingtao County District. So how to make batch modifications? There is no Replace in MongoDB.

Update, it's not easy to write code.

Finally. We still found a way.

Let's first take a look at the executed code and the results of the implementation.

Db.user.find ({'addresses.city': {' $regex': / Dingtao County, Heze City /}}). ForEach (function (item) {var tmp = String (item.addresses.city) tmp = tmp.replace ('Heze Dingtao County', 'Heze Dingtao County District') if (tmp = = null) {print (item.addresses.city)} item.addresses.city = tmp; db.user.save (item) })

After execution, view the data again, and the results are as follows:

/ * 1 createdAt:2019/2/28 9 p.m. 9 ObjectId 45 id 28 / {"_ id": ObjectId ("5c77e5f85f53771b74bc87e0"), "username": "hanmeimei", "email": "123456@126.com", "tel": 7891233, "addresses": {"provice": "Shandong Province", "city": "Caoji Town, Juancheng County, Heze City", "stree": "28 Wuzhuangyuan Street"}} / * 2 createdAt:2019/2/28 9 pm 9 ObjectId 45 5c77e5f85f53771b74bc87df 28 / {"_ id": ObjectId ("5c77e5f85f53771b74bc87df"), "username": "Lily", "email": "123456@126.com", "tel": 123455, "addresses": [{"provice": "Henan Province", "city": "Liji Town, Dengfeng County, Zhengzhou City" "stree": "28 Shaolin Street"}]}, / * 3 createdAt:2019/2/28 9 p.m. 45 ObjectId 28mm / {"_ id": ObjectId ("5c77e5f85f53771b74bc87de"), "username": "Lily", "email": "123456@126.com", "tel": 123455, "addresses": {"provice": "Shandong Province" "city": "Caoji Town, Dingtao County District, Heze City", "stree": "Wuzhuangyuan Street 28"}, / * 4 createdAt:2019/2/28 9 createdAt:2019/2/28 9 id 4515 28mm / {"_ id": ObjectId ("5c77e5f85f53771b74bc87dd"), "username": "jack", "email": "123456@126.com", "tel": 123455, "addresses": {"provice": "Shandong" "city": "Dingtao County District, Heze City", "stree": "28 Wuzhuangyuan Street"}}

In the collection document, the 'Dingtao County of Heze City' in addresses.city is all replaced by 'Dingtao County District of Heze City', which meets the design requirements.

3. Knowledge supplement and review

(1) as a database, MongoDB has a big advantage-it uses js to manage databases, so it can also use js scripts for complex management. MongoDB comes with JavaScript shell, and you can use the command line to interact with MongoDB in shell. Shell is a fully functional JavaScript interpreter that can run arbitrary JavaScript programs.

The shell of mongodb is implemented by javascript, so it is very intuitive and simple to use js directly to achieve the corresponding functions. For example, when we do statistical calculation on some data, in addition to using mapreduce and aggreate framework, it is also a good choice to use js directly. There are also some batches where data synchronization can be done using js.

For example, print out all the documents in the collection-- db.collectionname.find (). ForEach (printjson).

Note: the script for replacing characters in MongoDB demonstrated above has been verified in mongodb shell and nosqlbooster tools, no problem. But the same code in mongodb shell should pay attention to the format requirements, it is best to meet the requirements of Javascript format, otherwise, errors may be reported, such as: e QUERY [thread1] SyntaxError: missing; before statement @ (shell): 1QUERY 138

(2) the forEach:MongoDB database uses cursors to return the execution result of find. The implementation of cursors by the client usually has effective control over the final result. You can limit the number of results, skip some of the results, sort the results according to any combination of keys in any order, or perform other powerful operations. The cursor has an important iterative function-cursor.forEach (callback function), which allows us to customize the callback function to process each unit one by one.

(3) $regex: a regular expression uses a single string to describe and match a series of strings that conform to a syntactic rule. MongoDB uses the $regex operator to set the regular expression that matches the string.

For more information on the implementation of data replacement methods in MongoDB, please see the relevant links below

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.