查询某个字段不等于空字符串,must_not反向查找，不等于匹配值的结果集

查询某个字段的值不等于空字符串

GET aunt/aunt_type/_search
{"query": {"bool": {"must_not": [{"term": {"auntUserId": {"value": ""}}}]}}
}

must_not反向查找，不等于匹配值的结果集

GET /test2/product/_search
{"query": {"bool": {"must_not": [{"match": {"age": 20}},{"match": {"name": "test11"}}]}}
}

查询后按照某个字段排序

GET /test2/product/_search
{"query": {"match": {"name": "test"} },"sort": [{"age": {"order": "desc"}}]
}

分页查询

GET /test2/product/_search
{"query": {"match": {"name": "test"}},"sort": [{"age": {"order": "desc"}}],#从第几条开始"from": 0,#获取几条数据"size": 2#from + size其实就是limit from,size}

多条件or查询，是否存在（返回boolean值）

GET /test2/product/_search
{"query": {"bool": {#must的两个条件都必须满足，即mysql中的and#should的两个条件至少满足一个就可以。即mysql中的or"must": [{"match": {"name": "test"}},{"match": {"age": 20}}]}}
}

过滤（范围查询），符合条件的值查出来

GET /test2/product/_search
{"query": {"bool": {"must": [{"match": {"name": "test"}}], "filter": {"range": {"age": {#gte >=#lte <=#lt <#gt >"gte": 10,"lte": 19}}}}}
}

查询一个字段有多个值符合条件（in查询）

多个值用空格隔开

GET /test2/product/_search
{"query": {"match": {"name": "test 111111"}}
}

es查询某个字段不为null且不为空

sql 就是 where aunt_user_id is not null and aunt_user_id != ''

GET aunt/aunt_type/_search
{"query": {"bool": {"must": {"exists": {"field": "auntUserId"}},"must_not": [{"term": {"auntUserId": {"value": ""}}}]}}
}

es查询某个字段为null或者为空

sql 就是 where aunt_user_id is null or aunt_user_id = ''

GET aunt/aunt_type/_search
{"query": {"bool": {"should": [{"term": {"auntUserId": {"value": ""}}},{"bool": {"must_not": [{"exists":{"field": "auntUserId"}}]}}]}}
}

es查询数组是空即长度=0

GET shop_index/shop_type/_search
{"query": {"script": {"script": "doc['parentCategories'].getLength()==0"}},"sort": [{"parentCategories": {"order": "desc"}}]
}

分组聚合distinct

sql

select distinct auntName from xxx

GET /aunt/aunt_type/_search
{"query": {"term": {"shopId": {"value": "122697705213399040"}}},"collapse": {"field": "auntName"}
}

group by order by

select count(1),xxx 
from xxx 
group by xxx order by count(1) desc

我这里用的es版本是5.6.10

BoolQueryBuilder finalQueryBuilders = QueryBuilders.boolQuery();
finalQueryBuilders.must(QueryBuilders.termQuery("isDeleted", false));
finalQueryBuilders.must(QueryBuilders.termQuery("shopId", shopId));
finalQueryBuilders.must(QueryBuilders.existsQuery("cooperativeBusinessName"));
finalQueryBuilders.mustNot(QueryBuilders.termQuery("cooperativeBusinessName", ""));if(params != null && !StringUtils.isEmpty(params.getCooperativeBusinessNameKeyWord())){finalQueryBuilders.must(QueryBuilders.wildcardQuery("cooperativeBusinessName", "*" + params.getCooperativeBusinessNameKeyWord() + "*"));
}//2.构建查询
NativeSearchQueryBuilder nativeSearchQueryBuilder = new NativeSearchQueryBuilder();
//2.0 设置QueryBuilder
nativeSearchQueryBuilder.withQuery(finalQueryBuilders);
nativeSearchQueryBuilder.withSearchType(SearchType.QUERY_THEN_FETCH);//指定索引的类型，只先从各分片中查询匹配的文档，再重新排序和排名，取前size个文档
nativeSearchQueryBuilder.withIndices(AUNT_INDEX).withTypes(AUNT_TYPE);//指定要查询的索引库的名称和类型，其实就是我们文档@Document中设置的indedName和type
Integer size = params == null || params.getSize() == null? 5: params.getSize();
TermsAggregationBuilder termsAggregation = AggregationBuilders.terms("coopCount").field("cooperativeBusinessName").order(Terms.Order.count(false)).size(size);
nativeSearchQueryBuilder.addAggregation(termsAggregation);
//2.4构建查询对象
NativeSearchQuery nativeSearchQuery = nativeSearchQueryBuilder.build();
Aggregations aggregations = elasticsearchTemplate.query(nativeSearchQuery, new ResultsExtractor<Aggregations>() {@Overridepublic Aggregations extract(SearchResponse response) {return response.getAggregations();}
});Map<String, Aggregation> aggregationMap = aggregations.asMap();
//获得对应的聚合函数的聚合子类，该聚合子类也是个map集合,里面的value就是桶Bucket，我们要获得Bucket
StringTerms stringTerms = (StringTerms) aggregationMap.get("coopCount");
//获得所有的桶
List<StringTerms.Bucket> buckets = stringTerms.getBuckets();
//将集合转换成迭代器遍历桶,当然如果你不删除buckets中的元素，直接foreach遍历就可以了
Iterator<StringTerms.Bucket> iterator = buckets.iterator();
List<String> resultNames = Lists.newArrayList();
while(iterator.hasNext()) {//bucket桶也是一个map对象，我们取它的key值就可以了StringTerms.Bucket bucket = iterator.next();resultNames.add(bucket.getKeyAsString());
}return resultNames;