elasticsearch 中复杂查询的问题

[rqxiao]

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

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]

{
"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"
}
}
}
}
}

[arloor]

看下官方文档中 date 和 date_range 这两个数据类型，相信就能解决

[helloZwq]

GET index/_search
{
"query": {
"bool": {
"must": [
{
"range": {
"date": {
"gte": "2020-05-03 11:00:00",
"lte": "2020-06-03 12:00:00",
"format":"yyyy-MM-dd HH:mm:ss"
}
}
}
],
"must_not": [
{
"terms": {
"xxx.keyword": [
"1",
"2"
]
}
}
]
}
}
}

[ben1024]

DATE_FORMAT 可以用 format 来处理
not in 可以用 query string Ｎ OT AND 来处理或 must_not 嵌套处理