MongoDB增删改查等等一些...
Step1: 设计表
给定数据模型：用户-部门，多对多关系。
用户的departments属性是一个数组，表示用户的一个或多个部门。
//用户表字段
user{
id: String, //用户编号
name: String,
departments: Array, //用户的部门数组
gender: String,
birthDate: Date,
description: String
}
//部门表字段
department{
id: String, //部门编号
name: String,
description: String
} 其中departments字段存储部门的_id。
Step2: 表内容批量生成
1.插入
首先先定义用户数组user，然后循环生成各字段数据，存入user中，最后使用mongo的insertMany()方式，批量插入。
部门表也类似。
//------------------------生成用户表---------------------------------//
var users = []
//用户编号以流水号自增，用户名也加入数字，仅作不同区分，性别统一插入为'male'
for(i = 1; i < 100; i++){
users[i - 1] = (
{
id:"20200811" + i,
name:"bob" + i,
gender:"male",
birthDate:new Date(),
departments:[],
description:"this is the " + i + "th user that inserted in"
}
)
}
//将用户数组直接用insertMany批量插入
db.user.insertMany(users)
//------------------------生成部门表---------------------------------//
var depts = []
for(i = 1; i < 11; i++){
depts[i - 1] = (
{
id: i,
name:"deptNo." + i,
users:[],
description:"this is the " + i + "th department"
}
)
}
db.department.insertMany(depts) 2.更新
更新id为偶数的用户，将性别gender字段更新为female。
var count = db.user.find().count()
//更新id为双数的用户gender字段
for (i = 1; i <= count; i++){
if ( i % 2 == 0){
db.user.update(
{
id: "20200811" + i
},
{
/**
* field: The field name
* expression: The expression.
*/
$set: {
gender: "female"
}
}
)
}
} Step3: 插入用户-部门关联数据
为用户表中每个用户文档中的department字段，随机分配三个部门表的_id。部门表id的随机查找输出是使用aggregate([{$sample:{size:n}}])实现，并保存至depts变量中，值得注意的是：depts变量中保存的并不是直接的部门文档数据，具体的部门文档数据是在属性_batch下，然后对文档数据进行遍历，取出_id数组idList，_最终将_id数组保存至用户文档中。
db.user.find().forEach(
//为每个用户随机分配三个部门
function(item){
var depts = []
depts = db.department.aggregate([
{$sample: {size: 3}}
])
idList = []
depts._batch.forEach(
function(dept){
idList.push(dept._id)
}
)
//更新该用户的部门列表
db.user.update(
{id:item.id},
{$set:{department:idList}}
)
}
) 插入之后的结果如下
可以看出，用户文档中的department属性已经加入了随机的三个部门文档的_id。
/* 1 */
{
"_id" : ObjectId("5f3263d90438fed32d60c864"),
"id" : "202008111",
"name" : "bob1",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c931"),
ObjectId("5f3264770438fed32d60c92d")
],
"description" : "this is the 1th user that inserted in"
}
/* 2 */
{
"_id" : ObjectId("5f3263d90438fed32d60c865"),
"id" : "202008112",
"name" : "bob2",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c934"),
ObjectId("5f3264770438fed32d60c92e"),
ObjectId("5f3264770438fed32d60c930")
],
"description" : "this is the 2th user that inserted in"
}
/* 3 */
{
"_id" : ObjectId("5f3263d90438fed32d60c866"),
"id" : "202008113",
"name" : "bob3",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92d"),
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c932")
],
"description" : "this is the 3th user that inserted in"
}
/* 4 */
{
"_id" : ObjectId("5f3263d90438fed32d60c867"),
"id" : "202008114",
"name" : "bob4",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c931"),
ObjectId("5f3264770438fed32d60c92d"),
ObjectId("5f3264770438fed32d60c92e")
],
"description" : "this is the 4th user that inserted in"
}
/* 5 */
{
"_id" : ObjectId("5f3263d90438fed32d60c868"),
"id" : "202008115",
"name" : "bob5",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c933"),
ObjectId("5f3264770438fed32d60c92e"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 5th user that inserted in"
} 3.查询
希望的查询结果：查询名称为deptNo1的部门下的所有用户
$lookup
使用Mongo的聚合方法aggregate()来实现关联查询与字段显示，其中$lookup关键字表示多表关联查询，from表示需要关联的表department（base表为user）,localField表示base表需要关联的字段，foreign表示关联的表的对应字段。
因为user表中存储的是department表中的_id属性数组：departments，所以localField与foreignField需要填入_id与departments字段。
as表示在查询后，将department中的文档记录以user的一个属性显示。下面的代码中选择直接覆盖原属性departments来显示。
$match
$match表示查询的匹配条件，这里选择匹配关联表department中的名称为deptNo1。
limit
$skip:n方法表示跳过前n个数据，不进行罗列。$limit:n方法表示只显示查询到的前n个数据，结合skip与limit可以很简洁的实现分页操作。
如每页显示20条，显示第3页的数据，那么```limit:20```就可以实现。
skip, limit, sort的优先级为：sort, skip, limit。
$unwind
因为部门信息是以数组形式存储在用户表中，而数组中每个部门都是不同的，因此可以使用$unwind方式首先将数组拆分开，与用户文档一一对应，然后再去关联查询匹配，分页等，数据的结构更加的简洁。
db.user.aggregate(
[
{
$unwind: "$departments"
},
{
$lookup:{
from:"department",
localField:"departments",
foreignField:"_id",
as:"departments"
}
},
//查询部门名称为deptNo.2的关联后文档数据
{
$match:{
"department.name":{$eq:"deptNo.2"}
}
},
{
$skip:9
},
{
$limit:3
}
]
) 查询条件：找出部门名为deptNo2的用户列表，并给出deptNo2的用户信息。
分页参数：第4页，每页显示3条数据。
查询结果如下：
/* 1 */
{
"_id" : ObjectId("5f3263d90438fed32d60c894"),
"id" : "2020081149",
"name" : "bob49",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
{
"_id" : ObjectId("5f3264770438fed32d60c92c"),
"id" : 2.0,
"name" : "deptNo.2",
"description" : "this is the 2th department"
}
],
"description" : "this is the 49th user that inserted in"
}
/* 2 */
{
"_id" : ObjectId("5f3263d90438fed32d60c899"),
"id" : "2020081154",
"name" : "bob54",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
{
"_id" : ObjectId("5f3264770438fed32d60c92c"),
"id" : 2.0,
"name" : "deptNo.2",
"description" : "this is the 2th department"
}
],
"description" : "this is the 54th user that inserted in"
}
/* 3 */
{
"_id" : ObjectId("5f3263d90438fed32d60c89b"),
"id" : "2020081156",
"name" : "bob56",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
{
"_id" : ObjectId("5f3264770438fed32d60c92c"),
"id" : 2.0,
"name" : "deptNo.2",
"description" : "this is the 2th department"
}
],
"description" : "this is the 56th user that inserted in"
} Step4: 交换部门1与部门2的用户
思考：部门是以数组的形式作为用户的属性，那么对不同部门用户之间的交换，本质上可以转化为用户的部门_id的交换。
交换的主要步骤如下：
1.先找出部门1与部门2的_id
2.再找出部门1与部门2的用户
3.删除原有用户表中部门1与部门2的_id
4.将未删除前原用户具有部门1_id的，删除后，追加部门2的_id，将未删除前原用户具有部门2_id的，删除后，追加部门1的_id。
实际上步骤复杂了，其实可以直接对用户部门列表进行swap操作。
//findOne找出部门1的_id
var depart1 = db.department.findOne(
{
"name": "deptNo.1"
}
)._id
//findOne找出部门2的_id
var depart2 = db.department.findOne(
{
"name": "deptNo.2"
}
)._id
//找出属于部门1的所有用户
var user_depart1 = db.user.find(
{
"department":depart1
}
)
//找出属于部门2的所有用户
var user_depart2 = db.user.find(
{
"department": depart2
}
)
//输出部门1与部门2的用户
user_depart1.toArray()
user_depart2.toArray()
//删除所有用户中部门1与部门2的信息
db.user.updateMany(
{
},
{
$pull:{
"department": {
$in: [depart1,depart2]
}
}
}
)
//将原部门1的用户加入部门2
user_depart1.toArray().forEach(
function(item){
db.user.update(
{
"_id": item._id
},
{
$push:{
"department": depart2
}
}
)
}
)
//将原部门2的用户加入部门1
user_depart2.toArray().forEach(
function(item){
db.user.update(
{
"_id": item._id
},
{
$push:{
"department": depart1
}
}
)
}
) 上述代码中，使用
user_depart1.toArray() user_depart2.toArray()
来输出每个部门用户。
输出结果验证
toArray()的输出是在交换之前，因此为了获得交换后的部门下用户结果，只需执行两次即可。第一次是未交换的，第二次是进行第二次交换之前的，即前一次的输出结果。
原先部门1的用户(前5个)
user_depart1.toArray()
{
"_id" : ObjectId("5f3263d90438fed32d60c868"),
"id" : "202008115",
"name" : "bob5",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c933"),
ObjectId("5f3264770438fed32d60c92e"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 5th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c86a"),
"id" : "202008117",
"name" : "bob7",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c934"),
ObjectId("5f3264770438fed32d60c933"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 7th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c86d"),
"id" : "2020081110",
"name" : "bob10",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c930"),
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 10th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c86f"),
"id" : "2020081112",
"name" : "bob12",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c930"),
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 12th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c874"),
"id" : "2020081117",
"name" : "bob17",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c934"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 17th user that inserted in"
}, 原先部门2的用户(前5个)
user_depart2.toArray()
{
"_id" : ObjectId("5f3263d90438fed32d60c86c"),
"id" : "202008119",
"name" : "bob9",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c931"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 9th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c86e"),
"id" : "2020081111",
"name" : "bob11",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c92d"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 11th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c870"),
"id" : "2020081113",
"name" : "bob13",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c933"),
ObjectId("5f3264770438fed32d60c931"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 13th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c875"),
"id" : "2020081118",
"name" : "bob18",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92e"),
ObjectId("5f3264770438fed32d60c930"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 18th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c878"),
"id" : "2020081121",
"name" : "bob21",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92e"),
ObjectId("5f3264770438fed32d60c930"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 21th user that inserted in"
}, 更改后的部门1用户：
{
"_id" : ObjectId("5f3263d90438fed32d60c86c"),
"id" : "202008119",
"name" : "bob9",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c931"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 9th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c86e"),
"id" : "2020081111",
"name" : "bob11",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c92d"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 11th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c870"),
"id" : "2020081113",
"name" : "bob13",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c933"),
ObjectId("5f3264770438fed32d60c931"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 13th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c875"),
"id" : "2020081118",
"name" : "bob18",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92e"),
ObjectId("5f3264770438fed32d60c930"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 18th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c878"),
"id" : "2020081121",
"name" : "bob21",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92e"),
ObjectId("5f3264770438fed32d60c930"),
ObjectId("5f3264770438fed32d60c92b")
],
"description" : "this is the 21th user that inserted in"
}, 更改后的部门2用户：
{
"_id" : ObjectId("5f3263d90438fed32d60c868"),
"id" : "202008115",
"name" : "bob5",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c933"),
ObjectId("5f3264770438fed32d60c92e"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 5th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c86a"),
"id" : "202008117",
"name" : "bob7",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c934"),
ObjectId("5f3264770438fed32d60c933"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 7th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c86d"),
"id" : "2020081110",
"name" : "bob10",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c930"),
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 10th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c86f"),
"id" : "2020081112",
"name" : "bob12",
"gender" : "female",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c930"),
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 12th user that inserted in"
},
{
"_id" : ObjectId("5f3263d90438fed32d60c874"),
"id" : "2020081117",
"name" : "bob17",
"gender" : "male",
"birthDate" : ISODate("2020-08-11T09:24:41.589Z"),
"department" : [
ObjectId("5f3264770438fed32d60c92f"),
ObjectId("5f3264770438fed32d60c934"),
ObjectId("5f3264770438fed32d60c92c")
],
"description" : "this is the 17th user that inserted in"
}, 可以看出，部门1与部门2的结果发生了交换。
字节跳动公司氛围 560人发布
