ES查询练习

1.创建mysql表
CREATE TABLE persions (
id varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
name varchar(255) DEFAULT NULL,
sex varchar(255) DEFAULT NULL,
sect varchar(255) DEFAULT NULL,
age int DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.在mysql表中创建测试数据
INSERT INTO test.persions(id, name, sex, sect, age) VALUES (‘001’, ‘张无忌’, ‘男’, ‘明教’, 20);
INSERT INTO test.persions(id, name, sex, sect, age) VALUES (‘002’, ‘小王’, ‘男’, ‘峨眉’, 30);
INSERT INTO test.persions(id, name, sex, sect, age) VALUES (‘003’, ‘老王’, ‘男’, ‘少林寺’, 40);
INSERT INTO test.persions(id, name, sex, sect, age) VALUES (‘004’, ‘老李’, ‘男’, ‘天山’, 50);
INSERT INTO test.persions(id, name, sex, sect, age) VALUES (‘005’, ‘小赵’, ‘女’, ‘天山’, 10);
INSERT INTO test.persions(id, name, sex, sect, age) VALUES (‘006’, ‘小张’, ‘女’, ‘少林寺’, 20);
INSERT INTO test.persions(id, name, sex, sect, age) VALUES (‘007’, ‘董存瑞’, ‘男’, ‘明教’, 60);
INSERT INTO test.persions(id, name, sex, sect, age) VALUES (‘008’, ‘小日本’, ‘男’, ‘明教’, 10);
INSERT INTO test.persions(id, name, sex, sect, age) VALUES (‘009’, ‘小丽’, ‘女’, ‘天山’, 70);
3.ES创建表
PUT app_test/persions
{
“mappings”: {
“_doc”:{
“properties”: {
“id”: { “type”: “text” },
“name”: { “type”: “text” },
“sex”: { “type”: “text” },
“sect”: { “type”: “text” },
“age”: { “type”: “integer” }
//“created”: {
//“type”: “date”,
//“format”: “strict_date_optional_time||epoch_millis”
//}
}
}
}
}
4.将测试数据导入ES中
Java批量插入
List list = new ArrayList();
list.add(new IndexQueryBuilder().withObject(item).build());
elasticsearchTemplate.bulkIndex(list);

查看结果
GET app_test/persions/_search
5.在E S中执行如下查询
1.select * from persons where name = ‘张无忌’;
GET app_test/persions/_search
{
“query”:{
“term”:{
“name.keyword”:{
“value”:“张无忌”,
“boost”:1.0
}
}
}
}
java:QueryBuilders.termQuery(“name.keyword”,“张无忌”);//term
2.select * from pesons where age between 18 and 22;
GET app_test/persions/_search
{
“query”:{
“range”:{
“age”:{
“from”:18,
“to”:22,
“include_lower”:true,
“include_upper”:true,
“boost”:1.0
}

    }
}

}
java:QueryBuilders.rangeQuery(“age”).gte(18).lte(22)//range
3.select * from persons where name like ‘张%忌’;
GET app_test/persions/_search
{
“query”:{
“wildcard”:{
“name.keyword”:{
“wildcard”:“张忌",
“boost”:1.0
}
}
}
}
java:QueryBuilders.wildcardQuery(“name.keyword”,"张忌”);//wildcard
4.select * from persons where sex = ‘女’ and sect = ‘明教’;
GET app_test/persions/_search
{
“query”:{
“bool”:{
“must”:[
{
“term”:{
“sex.keyword”:{
“value”:“女”,
“boost”:1.0
}
}
},
{
“term”:{
“sect.keyword”:{
“value”:“明教”,
“boost”:1.0
}
}
}
]
}
}
}
java:QueryBuilders.boolQuery();//bool
List queryarr=new ArrayList<>();
queryarr.add(QueryBuilders.termQuery(“sect.keyword”,“明教”));
queryarr.add(QueryBuilders.termQuery(“sex.keyword”,“女”));
boolQueryBuilder.must().addAll(queryarr);
5.select max(age) from persons;
GET app_test/persions/_search
{
“aggregations”:{
“max_age”:{
“max”:{
“field”:“age”
}
}
}
}
java:MaxAggregationBuilder agg = AggregationBuilders.max(“maxage”).field(“age”);
6.select sect,count(id) from mytest.persons group by sect;
GET app_test/persions/_search
{
“aggregations”: {
“sect_count”: {
“terms”: {
“field”: “sect.keyword”,
“size”: 10,
“min_doc_count”: 1,
“shard_min_doc_count”: 0,
“show_term_doc_count_error”: false,
“order”: [
{
“_count”: “desc”
},
{
“_key”: “asc”
}
]
}
}
}
}
java:TermsAggregationBuilder agg = AggregationBuilders.terms(“sect_count”).field(“sect.keyword”);
7.select count(distinct sect) from persons;
GET app_test/persions/_search
{
“aggregations”:{
“count_age”:{
“cardinality”:{
“field”:“sect.keyword”
}
}
}
}
java:CardinalityAggregationBuilder agg = AggregationBuilders.cardinality(“sect_name”).field(“sect.keyword”);
8.select sect,sex,count(id) from mytest.persons group by sect,sex;
一起求count
先对sex求aggregations，在对结果和sect求aggregations
GET app_test/persions/_search
{
“aggregations”: {
“sect_count”: {
“terms”: {
“field”: “sect.keyword”,
“size”: 10
},
“aggregations”: {
“sex_count”: {
“terms”: {
“field”: “sex.keyword”,
“size”: 10
}
}
}
}
}
}

java:TermsAggregationBuilder agg = AggregationBuilders.terms(“sect_count”).field(“sect.keyword”);
TermsAggregationBuilder agg2 = AggregationBuilders.terms(“sex_count”).field(“sex.keyword”);
agg.subAggregation(agg2);
备注：
分别求countA count B
GET app_test/persions/_search
{
“aggregations”:{
“sect_count”:{
“terms”:{
“field”:“sect.keyword”,
“size”: 10
}
},
“sex_count”:{
“terms”:{
“field”:“sex.keyword”,
“size”:10
}
}
}
}
TermsAggregationBuilder aggsex = AggregationBuilders.terms(“sex_count”).field(“sex.keyword”);
TermsAggregationBuilder aggsect = AggregationBuilders.terms(“sect_count”).field(“sect.keyword”);
SearchQuery searchQuery = new NativeSearchQueryBuilder()
.addAggregation(aggsex)
.addAggregation(aggsect)
.build();

ES练习项目： https://github.com/QingYang12/elastic_search_test