2020-08-12 18:04 Java

关注

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。

$skip和$ limit

$skip:n方法表示跳过前n个数据，不进行罗列。$limit:n方法表示只显示查询到的前n个数据，结合skip与limit可以很简洁的实现分页操作。

如每页显示20条，显示第3页的数据，那么``` $skip:20*(3-1)``` ```$ 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的结果发生了交换。