MongoDB 行转列和列转行 - Java 代码示例及数据内容

在MongoDB中实现行转列和列转行的效果可以通过使用聚合管道和一些聚合操作符来实现。\n\n首先，我们假设有一个包含学生姓名、科目和成绩的集合，可以使用以下代码插入示例数据：\n\njava\nimport com.mongodb.MongoClient;\nimport com.mongodb.client.MongoCollection;\nimport com.mongodb.client.MongoDatabase;\nimport org.bson.Document;\nimport java.util.Arrays;\nimport java.util.List;\n\npublic class Main {\n public static void main(String[] args) {\n // 连接到MongoDB\n MongoClient mongoClient = new MongoClient("localhost", 27017);\n // 获取数据库\n MongoDatabase database = mongoClient.getDatabase("test");\n // 获取集合\n MongoCollection<Document> collection = database.getCollection("scores");\n\n // 插入示例数据\n List<Document> documents = Arrays.asList(\n new Document("name", "Alice").append("subject", "Math").append("score", 90),\n new Document("name", "Alice").append("subject", "English").append("score", 85),\n new Document("name", "Bob").append("subject", "Math").append("score", 80),\n new Document("name", "Bob").append("subject", "English").append("score", 70),\n new Document("name", "Charlie").append("subject", "Math").append("score", 75),\n new Document("name", "Charlie").append("subject", "English").append("score", 95)\n );\n\n collection.insertMany(documents);\n\n System.out.println("Data inserted successfully");\n }\n}\n\n\n接下来，我们可以使用聚合管道来实现行转列和列转行的效果。\n\n行转列示例代码如下：\n\njava\nimport com.mongodb.MongoClient;\nimport com.mongodb.client.AggregateIterable;\nimport com.mongodb.client.MongoCollection;\nimport com.mongodb.client.MongoDatabase;\nimport org.bson.Document;\n\nimport java.util.Arrays;\nimport java.util.List;\n\nimport static com.mongodb.client.model.Aggregates.*;\nimport static com.mongodb.client.model.Accumulators.*;\n\npublic class Main {\n public static void main(String[] args) {\n // 连接到MongoDB\n MongoClient mongoClient = new MongoClient("localhost", 27017);\n // 获取数据库\n MongoDatabase database = mongoClient.getDatabase("test");\n // 获取集合\n MongoCollection<Document> collection = database.getCollection("scores");\n\n // 行转列\n AggregateIterable<Document> result = collection.aggregate(Arrays.asList(\n group("$name", push("scores", new Document("subject", "$subject").append("score", "$score"))),\n project(new Document("_id", 0)\n .append("name", "$_id")\n .append("scores", 1))\n ));\n\n for (Document document : result) {\n System.out.println(document.toJson());\n }\n }\n}\n\n\n列转行示例代码如下：\n\njava\nimport com.mongodb.MongoClient;\nimport com.mongodb.client.AggregateIterable;\nimport com.mongodb.client.MongoCollection;\nimport com.mongodb.client.MongoDatabase;\nimport org.bson.Document;\n\nimport java.util.Arrays;\n\nimport static com.mongodb.client.model.Aggregates.*;\nimport static com.mongodb.client.model.Accumulators.*;\n\npublic class Main {\n public static void main(String[] args) {\n // 连接到MongoDB\n MongoClient mongoClient = new MongoClient("localhost", 27017);\n // 获取数据库\n MongoDatabase database = mongoClient.getDatabase("test");\n // 获取集合\n MongoCollection<Document> collection = database.getCollection("scores");\n\n // 列转行\n AggregateIterable<Document> result = collection.aggregate(Arrays.asList(\n unwind("$scores"),\n group("$scores.subject", push("students", new Document("name", "$name").append("score", "$scores.score"))),\n project(new Document("_id", 0)\n .append("subject", "$_id")\n .append("students", 1))\n ));\n\n for (Document document : result) {\n System.out.println(document.toJson());\n }\n }\n}\n\n\n以上代码中，我们使用了MongoDB的聚合管道操作符，group用于分组，push用于将文档添加到数组中，project用于投影。\n\n行转列的实现中，我们首先按照name字段分组，然后使用push将每个学生的科目和成绩添加到一个数组中，最后使用project进行投影，去除_id字段。\n\n列转行的实现中，我们首先使用unwind将scores数组展开为多个文档，然后按照scores.subject字段分组，使用push将每个学生的姓名和成绩添加到一个数组中，最后使用project进行投影，去除_id字段。\n\n运行以上代码，将会输出行转列和列转行的结果。\n\n行转列的输出结果示例：\n\njson\n{\n "name" : "Alice",\n "scores" : [\n {\n "subject" : "Math",\n "score" : 90\n },\n {\n "subject" : "English",\n "score" : 85\n }\n ]\n}\n{\n "name" : "Bob",\n "scores" : [\n {\n "subject" : "Math",\n "score" : 80\n },\n {\n "subject" : "English",\n "score" : 70\n }\n ]\n}\n{\n "name" : "Charlie",\n "scores" : [\n {\n "subject" : "Math",\n "score" : 75\n },\n {\n "subject" : "English",\n "score" : 95\n }\n ]\n}\n\n\n列转行的输出结果示例：\n\njson\n{\n "subject" : "Math",\n "students" : [\n {\n "name" : "Alice",\n "score" : 90\n },\n {\n "name" : "Bob",\n "score" : 80\n },\n {\n "name" : "Charlie",\n "score" : 75\n }\n ]\n}\n{\n "subject" : "English",\n "students" : [\n {\n "name" : "Alice",\n "score" : 85\n },\n {\n "name" : "Bob",\n "score" : 70\n },\n {\n "name" : "Charlie",\n "score" : 95\n }\n ]\n}\n\n\n希望以上代码对您有所帮助。