spring 使用聚合Aggregation substr mongo 查询

import com.xxx.repository.kpi.entity.ChannelCheckBase;
import com.xxx.repository.kpi.entity.DataJson;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.ScriptOperations;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.script.ExecutableMongoScript;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.util.Date;

/**
 * @autor wangy
 * @date 2021/3/16 13:33
 **/
@Service
public class ChannelResCountStatisticsService {

    private static final Logger logger = LoggerFactory.getLogger(ChannelResCountStatisticsService.class);
    @Autowired
    private MongoTemplate mongoTemplate;

    /**
     * 根据mongo中的目录ID、发布时间，统计每天、每个栏目下的资源量
     * @return
     */
    @Scheduled(cron = "0 0/1 * * * ?")
    public DataJson queryChannelResCountStatisticsByDay(){
        System.out.println("new date()===>" + new Date());
        DataJson dataJson = new DataJson();
        try {
            ScriptOperations scriptOps = mongoTemplate.scriptOps();
            String sqlStr = "db.getCollection(\"REPO_MANAGE_INFO\").aggregate(\n" +
                    "    {\n" +
                    "        $match: \n" +
                    "        {\n" +
                    "            'st': '3',\n" +
                    "            \"$and\": [{\n" +
                    "                \"metd.Pubtime\": {\n" +
                    "                    \"$gte\": \"2021-02-19 00:00:00\"\n" +
                    "                }\n" +
                    "            }, {\n" +
                    "                \"metd.Pubtime\": {\n" +
                    "                    \"$lte\": \"2021-02-20 23:59:59\"\n" +
                    "                }\n" +
                    "            }]\n" +
                    "        }\n" +
                    "    }\n" +
                    "    ,\n" +
                    "    {\n" +
                    "        $group: \n" +
                    "        {\n" +
                    "            \"_id\": {\n" +
                    "                dir: \"$dir\",\n" +
                    "                Year: {\n" +
                    "                    $substr: [\"$metd.Pubtime\", 0, 4]\n" +
                    "                },\n" +
                    "                Month: {\n" +
                    "                    $substr: [\"$metd.Pubtime\", 5, 2]\n" +
                    "                },\n" +
                    "                Day: {\n" +
                    "                    $substr: [\"$metd.Pubtime\", 8, 2]\n" +
                    "                }\n" +
                    "            },\n" +
                    "            \"orderNum\": {\n" +
                    "                \"$sum\": 1\n" +
                    "            }\n" +
                    "        }\n" +
                    "    }\n" +
                    ");";
            ExecutableMongoScript echoScript = new ExecutableMongoScript(sqlStr);
            // 执行mongo语句，并得到查询结果
            Object directly_execute_script = scriptOps.execute(echoScript, "directly execute script");

            if (1==1) {
                dataJson.setMsg("数据保存成功！");
                dataJson.setStatus("0");
            } else {
                dataJson.setMsg("数据保存失败！");
                dataJson.setStatus("1");
            }
            return dataJson;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            dataJson.setMsg(e.getMessage());
            dataJson.setStatus("1");
            return dataJson;
        }
    }
} 

import com.xxx.repository.kpi.entity.ChannelCheckBase;
import com.xxx.repository.kpi.entity.DataJson;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.ScriptOperations;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.script.ExecutableMongoScript;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.util.Date;

/**
 * @autor wangy
 * @date 2021/3/16 13:33
 **/
@Service
public class ChannelResCountStatisticsService {

    private static final Logger logger = LoggerFactory.getLogger(ChannelResCountStatisticsService.class);
    @Autowired
    private MongoTemplate mongoTemplate;

    /**
     * 根据mongo中的目录ID、发布时间，统计每天、每个栏目下的资源量
     * @return
     */
    @Scheduled(cron = "0 0/1 * * * ?")
    public DataJson queryChannelResCountStatisticsByDay(){
        System.out.println("new date()===>" + new Date());
        DataJson dataJson = new DataJson();
        try {
            Criteria criteria = Criteria.where("st").is("3");
            criteria.andOperator(Criteria.where("metd.Pubtime").lte("2021-02-20 23:59:59"),
                    Criteria.where("metd.Pubtime").gte("2021-02-19 00:00:00"));
            Aggregation customerAgg = Aggregation.newAggregation(
                    Aggregation.match(criteria),
                    Aggregation.project("dir","year","month","day")
                            .andExpression("{ dir: '$dir'," +
                            "                year: { " +
                            "                    $substr: {'$metd.Pubtime', 0, 4}" +
                            "                }," +
                            "                Month: {" +
                            "                    $substr: {'$metd.Pubtime', 5, 2}" +
                            "                }," +
                            "                Day: {" +
                            "                    $substr: {'$metd.Pubtime', 8, 2}" +
                            "                } " +
                            "}").as("_id"),
                    Aggregation.group("_id").count().as("updateNum")
            );
            // 进行查询，并得到查询结果
            AggregationResults<ChannelCheckBase> aggregate = mongoTemplate.aggregate(customerAgg, "REPO_MANAGE_INFO", ChannelCheckBase.class);
            aggregate.getMappedResults();
            if (1==1) {
                dataJson.setMsg("数据保存成功！");
                dataJson.setStatus("0");
            } else {
                dataJson.setMsg("数据保存失败！");
                dataJson.setStatus("1");
            }
            return dataJson;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            dataJson.setMsg(e.getMessage());
            dataJson.setStatus("1");
            return dataJson;
        }
    }
}