Millet Porridge

English version of https://corvo.myseu.cn

0%

First Steps with MongoDB

Posted on Edited on In Disqus:
I hadn't actually used MongoDB before, but a company project happened to need it. Anyway, getting a slightly systematic understanding of MongoDB is a good thing. I'll share the problems I met at work and some solutions. I hope readers are familiar with MySQL first, then look at Join and Group by operations.

Updated 2020-06-18: added database migration work

MongoDB GUI

Although I prefer the command line, for databases, CRUD via a GUI is really convenient. Unfortunately MongoDB has no good tool like phpMyAdmin. After trying many tools, I finally chose Robo3t.

CRUD

Basic CRUD is definitely the most used in work. Although work uses PyMongo, here I only list the Mongo shell operations, which are basically similar to PyMongo’s.

1
2
3
4
5
6
7
8
9
10
11
12
// insert
db.new_doc.insert({'x': 123, 'y': 456});

// delete
db.new_doc.deleteOne({'x': 123});

// update
db.new_doc.insert({'x': 123, 'y': 456});
db.new_doc.update({'x': 123}, {'x': 234, 'y':456, 'z': 789});

// query
db.new_doc.find({'x': 234});

Building Indexes

  • View existing indexes: db.xxx.getIndexKeys() or db.xxx.getIndexes()

MongoDB also uses B+ trees for indexes, so after adding an index, range queries get decent efficiency, while many random point queries are a bit slower.

The detailed official documentation is here: MongoDB Indexes.

I’ll pick a few that were new knowledge for me, but please always defer to the official docs. If there are any errors, please point them out and I’ll update promptly:

  1. When MongoDB creates a collection, it automatically adds an index for the _id field.
  2. For single-field indexes, the sort direction doesn’t matter — ascending or descending, MongoDB will use it when querying. For multi-field indexes, they are organized by the field order used when creating the index and each field’s specified direction; if the query order differs from the index organization order, the index becomes ineffective.
  3. Partial indexes (version 3.2+): only entries meeting certain conditions are recorded by the index.
  4. Sparse indexes: only entries where a certain field exists are recorded by the index. The official docs recommend implementing this feature with partial indexes instead.
  5. TTL indexes: the indexed field can only be a Date type or an array of Date data; as soon as one Date value reaches its expiration, the record is deleted.

Using Explain

In MySQL, explain is definitely a good helper for optimizing queries — most importantly checking whether the inner search used an index. Here is the official explain usage, and a more detailed explain usage.

For now I only look at the stage in winningPlan; if it’s IXSCAN I’m basically at ease. That doesn’t mean other fields are unimportant; if I encounter more in the future I’ll update this blog.

Javascript and the Mongo shell

In the Mongo shell you can use Javascript code, but the Mongo shell is really hard to use. I suggest adding a file in Robo3t to run it. Below is a simple piece of code using Javascript to simulate a join operation (run it — it should be easy to understand; the point is that Javascript code can be used):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
db.example.insert({'name': 'a', 'code': 1})
db.example.insert({'name': 'b', 'code': 2})
db.example.insert({'name': 'c', 'code': 3})
db.example.insert({'name': 'd', 'code': 1})
db.expjoin.insert({'code': 1, 'content': 'This is for code 1'})
db.expjoin.insert({'code': 2, 'content': 'This is for code 2'})

// find certain names
var expArr = db.example.find(
    {
        'name': {$in: ['a', 'b']}
    }
).map((item) => {
        return  {'id': item._id, 'name': item.name, 'code': item.code};
    }
);

// look up for each element
expArr.forEach((e) => {
    e.details = db.expjoin.find({'code': e.code}).toArray();
});
print(expArr);

// function definition:
var myFunction = function () {
    var x = db.example.count();
    print(x);
}
myFunction();

Some Uses of Aggregate

Many operations in MongoDB cannot be done with a single statement; aggregate is suitable for simple computations that return results.

Official Aggregation documentation

Simulating Join ($lookup)

The $lookup operation was added in MongoDB versions after 3.2 — official Lookup documentation — similar to MySQL’s join operation.

lookup must be used with the aggregate statement — Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// achieves the same effect as the js code above
db.example.aggregate([
    {
        $match: {'name': {$in: ['a', 'b']}}
    },
    {
        $lookup: {
            "from": "expjoin",
            "localField": "code",
            "foreignField": "code",
            "as": "details"
        }
    }
])

Simulating Group by

The official documentation explains it in detail; groupby is also the first example of the Pipeline.

** View the database version: db.version(); **

aggregate and Mongo version problems

Different versions of the MongoDB shell and MongoDB server will raise warnings, and aggregate statements may also execute incorrectly.

1
2
3
4
MongoDB shell version v4.0.2
connecting to: mongodb://127.0.0.1:27000/test
MongoDB server version: 2.4.8
WARNING: shell and server versions do not match

I also encountered the error below during execution. If you also meet such a TypeError, you should consider using the original version of the MongoDB shell on the server.

1
2
3
4
5
> db.product.aggregate([{$group: {_id: "$p_id",count: { $sum: 1 }},},{$sort: {count: 1,},} ])
2018-10-09T20:43:24.460+0800 E QUERY    [js] TypeError: invalid assignment to const `res' :
DB.prototype._runAggregate@src/mongo/shell/db.js:250:19
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1056:12
@(shell):1:1

$ne, $eq version problems

$eq doesn’t exist in versions before 3.0. To achieve the effect of {<field>: { $eq: <value> }}, you can only use {"field": {'$not': {'$ne': <value>}}}. Honestly, using it this way is still a bit mechanical.

$lookup version problems

As said above, lookup is only supported after 3.2; on versions before 3.2 you can only join manually!!!

explain version problems

After 3.0:

You can use db.comment.explain().aggregate([...]) You can use db.comment.explain().find({...})

2.6 ~ 3.0:

To view explain information for db.comment.aggregate, use it like this:

1
2
3
4
5
6
7
db.comment.aggregate([
    ...
  ],
  {
    explain: true
  }
)

To use explain with db.comment.find: do this: db.admin.find({...}).explain()

Before 2.6:

aggregate cannot explain. To use explain with db.comment.find: do this: db.admin.find({...}).explain()

Really, upgrading MongoDB is quite necessary. Maybe many people think it’s easy for me to say. But trying a smooth database migration should also count as part of the job; what matters is getting the migration process and master/standby right, able to recover instantly.

Data migration work

MongoDB import/export work requires downloading some Database Tools; just choose the download for your system version.

1
2
3
4
5
# export data to users.json
./mongoexport mongodb://<user>:<pass>@<old_host>:<old_port>/admin --collection users --out users.json

# import data into the new database
./mongoimport mongodb://<user>:<pass>@<new_host>:<new_port>/admin --collection users --file users.json

MongoDB transactions

Only supported after 4.0. The company doesn’t use such a new version, nor MongoDB transactions. When I really use them I’ll share with everyone.