elasticsearch 中复杂查询的问题

想完成在数据库中这样的操作

SELECT coupon_type_id, count( * ) FROM coupon_statistics_record WHERE DATE_FORMAT( create_time, "%Y-%m" ) = '2020-05' AND coupon_type_id NOT IN ( '1', '2' ) AND counter_code = '111' GROUP BY coupon_type_id

现在用 ElasticsearchRepository 去做了聚合，剩下的 DATE_FORMAT 和 not in 不知道该怎么去做

    NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();

    // 不查询任何结果
    queryBuilder.withSourceFilter(new FetchSourceFilter(new String[]{""}, null));
    // 1 、添加一个新的聚合，聚合类型为 terms，聚合名称为 brands，聚合字段为 brand
    queryBuilder.addAggregation(
            AggregationBuilders.terms("couponTypeIds").field("couponTypeId.keyword"));
    // 2 、查询,需要把结果强转为 AggregatedPage 类型
    AggregatedPage<CouponStatisticsRecordESDto> aggPage = (AggregatedPage<CouponStatisticsRecordESDto>) couponStatisticsRecordESRepository.search(queryBuilder.build());
    // 3 、解析
    // 3.1 、从结果中取出名为 brands 的那个聚合，
    // 因为是利用 String 类型字段来进行的 term 聚合，所以结果要强转为 StringTerm 类型
    StringTerms agg = (StringTerms) aggPage.getAggregation("couponTypeIds");
    // 3.2 、获取桶
    List<StringTerms.Bucket> buckets = agg.getBuckets();
    // 3.3 、遍历
    for (StringTerms.Bucket bucket : buckets) {
        // 3.4 、获取桶中的 key，即品牌名称
        System.out.println(bucket.getKeyAsString());
        // 3.5 、获取桶中的文档数量
        System.out.println(bucket.getDocCount());
    }

rqxiao

2020-06-03 14:37:43 +08:00

{
"mapping": {
"CouponStatisticsRecord": {
"properties": {
"consultantCode": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"couponId": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"couponTypeId": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"createTime": {
"type": "long"
},
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"memberCode": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"verifyExtraInfo": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"verifyTime": {
"type": "long"
}
}
}
}
}