聊聊MongoDB（五）Aggregate Demo

最新推荐文章于 2022-12-05 14:36:17 发布

weixin_33835690

最新推荐文章于 2022-12-05 14:36:17 发布

阅读量153

点赞数

文章标签：数据库 json java

原文链接：https://my.oschina.net/lzhaoqiang/blog/845305

版权

2019独角兽企业重金招聘Python工程师标准>>>

首先列出所需部分数据

集合user：

/* 1 */
{
    "_id" : 1.0,
    "hire" : ISODate("2016-12-15T02:52:04.630Z")
}

/* 2 */
{
    "_id" : 2.0,
    "chinese" : 80.0,
    "math" : 90.0,
    "name" : "lisi"
}

/* 3 */
{
    "_id" : 3.0,
    "chinese" : 70.0,
    "math" : 90.0,
    "name" : "wangwu"
}

/* 4 */
{
    "_id" : 4.0,
    "name" : "zs",
    "chinese" : 50,
    "math" : 60
}

集合date：

/* 1 */
{
    "_id" : 1,
    "hire" : ISODate("2016-12-20T08:37:15.705Z")
}

集合mycol：

/* 1 */
{
    "_id" : ObjectId("57ec6d0d7fbbc90bccc18e13"),
    "title" : "MongoDB1",
    "description" : "nosql",
    "likes" : 0,
    "by_user" : "lzq"
}

/* 2 */
{
    "_id" : ObjectId("57ec6d0d7fbbc90bccc18e14"),
    "title" : "MongoDB2",
    "description" : "nosql",
    "likes" : 2,
    "by_user" : "lzq"
}

/* 3 */
{
    "_id" : ObjectId("57ec6d0d7fbbc90bccc18e15"),
    "title" : "MongoDB3",
    "description" : "nosql",
    "likes" : 3,
    "by_user" : "lzq"
}

集合tree：

/* 1 */
{
    "_id" : ObjectId("584e47110dc763ff7e50347b"),
    "management" : {
        "organization" : [ 
            {
                "name" : "Organizational institution",
                "index" : 1.0
            }, 
            {
                "name" : "Company files",
                "index" : 2.0
            }, 
            {
                "name" : "department files",
                "index" : 3.0
            }
        ],
        "authority" : [ 
            {
                "name" : "user",
                "sequence" : 100.0
            }, 
            {
                "name" : "role",
                "sequence" : 102.0
            }, 
            {
                "name" : {
                    "source_register" : [ 
                        {
                            "name" : "button"
                        }, 
                        {
                            "name" : "field"
                        }
                    ]
                },
                "sequence" : 103.0
            }
        ]
    },
    "num" : 1.0
}

Code：

聚合操作所有JSON定义类

package db.mongo.feature.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.util.Arrays;
import java.util.List;


public class AggregateJson{

    //$project操作
    public static final String USERPROJECT = "{$project:{\"userid\":\"$_id\",\"_id\":0}}";
    //$project操作与$add操作(示例：perStuTotalScore)
    public static final String PROJECT_ADD = "{\n" +
            "    $project:{\n" +
            "        chinese:1,\n" +
            "        math:1,\n" +
            "        totalgrade:{$add:[\"$chinese\",\"$math\"]}\n" +
            "        }\n" +
            "   }";

    //日期操作(示例date集合)
    public static final String DATE_EXPRESSION = "{\n" +
            "       $project:\n" +
            "         {\n" +
            "         year: { $year: \"$hire\" },\n" +
            "           month: { $month: \"$hire\" },\n" +
            "           day: { $dayOfMonth: \"$hire\" },\n" +
            "           hour: { $hour: \"$hire\" },\n" +
            "           minutes: { $minute: \"$hire\" },\n" +
            "           seconds: { $second: \"$hire\" },\n" +
            "           milliseconds: { $millisecond: \"$hire\" },\n" +
            "           dayOfYear: { $dayOfYear: \"$hire\" },\n" +
            "           dayOfWeek: { $dayOfWeek: \"$hire\" },\n" +
            "           week: { $week: \"$hire\" }\n" +
            "         }\n" +
            "     }";
    //截取name的第一个字节，与.和$sex值拼接(示例mycol集合)
    public static final String[] PROJECT_CONCAT_SUBSTR = {"{$project:{\"title\":{\n" +
            "  $concat:[\"$by_user\",\".\",{$substr:[\"$title\",5,8]}]\n" +
            "  }}}","{$limit:2}"};
    //比较两个数值大小(示例user集合)
    public static final String PROJECT_CMP = "{\n" +
            "    $project:{\n" +
            "        chinese:1,\n" +
            "        math:1,\n" +
            "        compareTo:{$cmp:[\"$chinese\",\"$math\"]}\n" +
            "        }\n" +
            "   }";

    public static final String[] PRJECT_GROUP_SORT_LIMIT = {"{   \n" +
            "        $project: {\n" +
            "        \"name\": 1,\n" +
            "        \"chinese\":1,\n" +
            "        \"math\":1\n" +
            "        }\n" +
            "    }", "{ $group: { _id: \"$name\" ,totalScore:{$sum:{$add:[\"$chinese\",\"$math\"]}}} }",  "{\n" +
            "        $sort: {\n" +
            "            \"totalScore\": -1\n" +
            "        }\n" +
            "    }", "{\n" +
            "        $limit: 3\n" +
            "    }"};

    public static final String PROJECT_COND = "{\n" +
            "         $project:\n" +
            "           {\n" +
            "             \n" +
            "             discount:\n" +
            "               {\n" +
            "               $cond: { if: { $gte: [ \"$chinese\", 60 ] }, then: \"$chinese\", else: \"不合格\" }\n" +
            "               }\n" +
            "           }\n" +
            "      }";

    public static final String UNWIND = "{$unwind:\"$management.organization\"}";

    public static final String DISTINCT = "{\"distinct\":\"user\",\"key\":\"chinese\"}";
}

聚合操作类：

package db.mongo.feature;

import com.mongodb.AggregationOptions;
import com.mongodb.GroupCommand;
import com.mongodb.MongoClient;
import com.mongodb.client.*;
import com.mongodb.client.model.Filters;
import com.mongodb.client.model.FindOptions;
import com.mongodb.client.model.Projections;
import com.mongodb.client.model.Sorts;
import com.mongodb.operation.GroupOperation;
import db.mongo.feature.util.AggregateJson;
import db.mongo.util.Constants;
import org.apache.log4j.Logger;
import org.bson.BsonDocument;
import org.bson.Document;
import org.bson.conversions.Bson;
import org.junit.Before;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class MongoAggregate {

    /*
    聚合：对集合中的文档进行变换和组合,可对文档进行一连串的处理.
    $match $project $group $unwind $limit $skip $sort...

    需要将聚合操作传给aggregate，db.articles.aggregate如果不是从数据库返回的集合，那么Mongo Shell会自动迭代20次
    MongoDB不允许管道占用很多内存，如20%,会直接报错
*/
/*$project代表将articles集合投射出author字段，结果集中只包含_id和author字段;
    $match匹配以x开头的author;
    然后"_id":"$author"指定了分组字段author;
    $group会在结果集中创建count字段,执行结果类似这样的结构:{ "_id" : "xiaobao", "count" : 10 };
    $sort按照count字段将以上结果排序;
    $limit将限制返回结果集的前三个文档
    "$filename"是为了引用字段的值
*/


    private static Logger logger = Logger.getLogger(MongoAggregate.class);
    MongoDatabase database = null;

    public  MongoCollection<Document> getCollection(String collectionName) {
        return this.database.getCollection(collectionName);
    }

    @Before
    public void connect(){
        MongoClient client = new MongoClient(Constants.MONGO_HOST,Constants.MONGO_PORT);
        //当前获取这个数据库时并没有这个名称的数据库，不会报错，当有数据添加的时候才会创建
        database = client.getDatabase("test");
    }

    /**
     * @param collection 接收一个指定的mongo集合
     * @param jsons      接收json数组，用来聚合查询时解析多个json过滤条件
     *
     *  接收多个json字符串
     */
    public void aggregate(MongoCollection<Document> collection,String... jsons){
        List<Document> pileline = new ArrayList<Document>();
        for (String json : jsons){
            pileline.add(Document.parse(json));
        }
        List<Document> results = collection.aggregate(pileline).into(new ArrayList<Document>());

        for (Document cur : results) {

            System.out.println(cur.toJson());
        }
    }

    /**
     * 测试所有聚合操作
     */
    @Test
    public void testAggregate() {
        /*$project操作
        MongoCollection<Document> collection = getCollection("user");
        aggregate(collection, AggregateJson.USERPROJECT);
        */


        /*查询日期
        MongoCollection<Document> collection = getCollection("date");
        aggregate(collection, AggregateJson.DATE_EXPRESSION);*/


        //$project操作与$add操作(示例：perStuTotalScore)
        /*MongoCollection<Document> collection = getCollection("user");
        aggregate(collection, AggregateJson.PROJECT_ADD);*/


        //截取name的第一个字节，与.和$sex值拼接(示例mycol集合)
        /*MongoCollection<Document> collection = getCollection("mycol");
        aggregate(collection, AggregateJson.PROJECT_CONCAT_SUBSTR);*/

        //学生总成绩排名前三甲
        /*MongoCollection<Document> collection = getCollection("user");
        aggregate(collection, AggregateJson.PRJECT_GROUP_SORT_LIMIT);*/

        /*比较成绩高低，返回比较记录，类似java的Comparable比较器的compareTo方法，返回1,0,-1*/
        /*MongoCollection<Document> collection = getCollection("user");
        aggregate(collection, AggregateJson.PROJECT_CMP);*/

        //判断语文成绩大小，如果大于60分显示其分数，如果小于60分则显示不合格,如果没有这个字段也会显示不合格
        /*MongoCollection<Document> collection = getCollection("user");
        aggregate(collection, AggregateJson.PROJECT_COND);*/

        //将数组的每个值拆分为单独的文档
        /*MongoCollection<Document> collection = getCollection("tree");
        aggregate(collection, AggregateJson.UNWIND);*/

        //distinct找出给定键不同的值,找出不同的语文成绩
/*        Document document = database.runCommand(Document.parse(AggregateJson.DISTINCT));
        System.out.println(document.toJson());*/

    }
}

转载于:https://my.oschina.net/lzhaoqiang/blog/845305