ElasticSearch《三：复杂查询加聚合》

oldFarmer
284 阅读3分钟

前言：准备示例数据如下 image.png 索引jmall_product的映射是

{
  "jmall_product" : {
    "mappings" : {
      "properties" : {
        "attrs" : {
          "type" : "nested",
          "properties" : {
            "attrId" : {
              "type" : "long"
            },
            "attrName" : {
              "type" : "keyword"
            },
            "attrValue" : {
              "type" : "keyword"
            }
          }
        },
        "brandId" : {
          "type" : "long"
        },
        "brandImg" : {
          "type" : "keyword"
        },
        "brandName" : {
          "type" : "keyword"
        },
        "catalogId" : {
          "type" : "long"
        },
        "catalogName" : {
          "type" : "keyword"
        },
        "catelogId" : {
          "type" : "long"
        },
        "hasStock" : {
          "type" : "boolean"
        },
        "hosStock" : {
          "type" : "boolean"
        },
        "hotScore" : {
          "type" : "long"
        },
        "saleCount" : {
          "type" : "long"
        },
        "skuId" : {
          "type" : "long"
        },
        "skuImg" : {
          "type" : "keyword"
        },
        "skuPrice" : {
          "type" : "keyword"
        },
        "skuTitle" : {
          "type" : "text",
          "analyzer" : "ik_smart"
        },
        "spuId" : {
          "type" : "long"
        }
      }
    }
  }
}

一：查询标题skuTitle含有Apple的数据

GET /jmall_product/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": {
          "skuTitle": "Apple"
        }}
      ]
    }
  }
}

image.png

二：根据catalogId=225继续查询

分析：由于filter不参与评分，所以我们使用filter来过滤

GET /jmall_product/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": {
          "skuTitle": "Apple"
        }}
      ],"filter":[
        {
        "term": {
          "catalogId": "225"
        }
      }
        ]
    }
  }
}

image.png

三：继续根据skuPrice价格区间检索

GET /jmall_product/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": {
          "skuTitle": "Apple"
        }}
      ],"filter":[
        {
        "term": {
          "catalogId": "225"
        }
      },{
        "term": {
          "brandId": "12"
        }
      },{
       "range": {
         "skuPrice": {
           "gte": 5500,
           "lte": 6800
         }
       }
      }
        ]
    }
  }
}

image.png

四：根据attrs检索

根据mapping得知attrs的type是nested，这里不能像catalogId那样直接使用term，具体应该怎么写呢，我们参考官网www.elastic.co/guide/en/el… 对nested有详细的介绍

GET /jmall_product/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": {
          "skuTitle": "Apple"
        }}
      ],"filter":[
        {
        "term": {
          "catalogId": "225"
        }
      },{
        "term": {
          "brandId": "12"
        }
      },{
       "range": {
         "skuPrice": {
           "gte": 5500,
           "lte": 6800
         }
       }
      },{
        "nested": {
          "path": "attrs",
          "query": {
            "term": {
              "attrs.attrId": {
                "value": "3"
              }
            }
          }
        }
      }
        ]
    }
  }
}

五：根据skuPrice降序排序

GET /jmall_product/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": {
          "skuTitle": "Apple"
        }}
      ],"filter":[
        {
        "term": {
          "catalogId": "225"
        }
      },{
        "term": {
          "brandId": "12"
        }
      },{
       "range": {
         "skuPrice": {
           "gte": 5500,
           "lte": 6800
         }
       }
      },{
        "nested": {
          "path": "attrs",
          "query": {
            "term": {
              "attrs.attrId": {
                "value": "3"
              }
            }
          }
        }
      }
        ]
    }
  },"sort": [
    {
      "skuPrice": {
        "order": "desc"
      }
    }
  ]

六：分页

es的分页主要靠from和size来实现

GET /jmall_product/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": {
          "skuTitle": "Apple"
        }}
      ],"filter":[
        {
        "term": {
          "catalogId": "225"
        }
      },{
        "term": {
          "brandId": "12"
        }
      },{
       "range": {
         "skuPrice": {
           "gte": 5500,
           "lte": 6800
         }
       }
      },{
        "nested": {
          "path": "attrs",
          "query": {
            "term": {
              "attrs.attrId": {
                "value": "3"
              }
            }
          }
        }
      }
        ]
    }
  },"sort": [
    {
      "skuPrice": {
        "order": "desc"
      }
    }
  ],
  "from": 0,
  "size": 5
}

七：检索字段高亮显示

es的高亮实现通过highlight，可以在返回结果中拼接css代码

"highlight": {
        "fields": {
      "skuTitle": {}
    },
    "pre_tags": "<b style='color:red'>",
    "post_tags": "</b>"
  }

八：对检索结果聚合

"aggs": {
    "brandIdAgg": {
      "terms": {
        "field": "brandId",
        "size": 10
      },
      "aggs": {
        "brandNameAgg": {
          "terms": {
            "field": "brandName",
            "size": 10
          }
        },
        "brandImgAgg": {
          "terms": {
            "field": "brandImg",
            "size": 10
          }
        }
      }
    },
    "catalogIdAgg": {
      "terms": {
        "field": "catalogId",
        "size": 10
      },
      "aggs": {
        "catalogNameAgg": {
          "terms": {
            "field": "catalogName",
            "size": 10
          }
        }
      }
    },
    "attrsAgg": {
      "nested": {
        "path": "attrs"
      },
      "aggs": {
        "attrIdAgg": {
          "terms": {
            "field": "attrs.attrId",
            "size": 10
          },
          "aggs": {
            "attrNameAgg": {
              "terms": {
                "field": "attrs.attrName",
                "size": 10
              }
            }
          }
        }
      }
    }
  }

九：完整代码

GET /jmall_product/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "skuTitle": "Apple"
          }
        }
      ],
      "filter": [
        {
          "term": {
            "catalogId": "225"
          }
        },
        {
          "terms": {
            "brandId": [
              "12"
            ]
          }
        },
        {
          "range": {
            "skuPrice": {
              "gte": 5000,
              "lte": 6900
            }
          }
        },
        {
          "nested": {
            "path": "attrs",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "attrs.attrId": {
                        "value": "12"
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "sort": [
    {
      "skuPrice": {
        "order": "desc"
      }
    }
  ],
  "from": 0,
  "size": 5,
  "highlight": {
    "fields": {
      "skuTitle": {}
    },
    "pre_tags": "<b style='color:red'>",
    "post_tags": "</b>"
  },
  "aggs": {
    "brandIdAgg": {
      "terms": {
        "field": "brandId",
        "size": 10
      },
      "aggs": {
        "brandNameAgg": {
          "terms": {
            "field": "brandName",
            "size": 10
          }
        },
        "brandImgAgg": {
          "terms": {
            "field": "brandImg",
            "size": 10
          }
        }
      }
    },
    "catalogIdAgg": {
      "terms": {
        "field": "catalogId",
        "size": 10
      },
      "aggs": {
        "catalogNameAgg": {
          "terms": {
            "field": "catalogName",
            "size": 10
          }
        }
      }
    },
    "attrsAgg": {
      "nested": {
        "path": "attrs"
      },
      "aggs": {
        "attrIdAgg": {
          "terms": {
            "field": "attrs.attrId",
            "size": 10
          },
          "aggs": {
            "attrNameAgg": {
              "terms": {
                "field": "attrs.attrName",
                "size": 10
              }
            }
          }
        }
      }
    }
  }
}

执行结果 image.png

十：结合java实现

10.1模糊匹配，过滤（按照属性，分类，品牌，价格区间，库存）
SearchSourceBuilder searchSourceBuilder=new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
SearchRequest searchRequest=new SearchRequest();
if(!StringUtils.isEmpty(searchParam.getKeyword())){
      boolQueryBuilder.must(QueryBuilders.matchQuery("skuTitle",searchParam.getKeyword()));
      //放在最后拼装searchSourceBuilder.query(boolQueryBuilder);
      //searchRequest.source(searchSourceBuilder);
  }
  if(searchParam.getCatalog3Id()!=null){
      boolQueryBuilder.filter(QueryBuilders.termQuery("catalog3Id",searchParam.getCatalog3Id()));
  }
  //由于brandId传过来的是list，所以还要判断size是否>0
  if(searchParam.getBrandId()!=null&&searchParam.getBrandId().size()>0){
      boolQueryBuilder.filter(QueryBuilders.termsQuery("brandId",searchParam.getBrandId()));
  }
  //判断是否有库存
  if(searchParam.getHasStock()!=null){
      boolQueryBuilder.filter(QueryBuilders.termQuery("hasStock",searchParam.getHasStock()==1));
  }
  //判断价格区间，和前端约定价格skuPrice默认格式为：1_500或_500或500_
  // 三种情况
  if(searchParam.getSkuPrice()!=null){
      String[] s = searchParam.getSkuPrice().split("_");
      if(s.length==1){
          if(searchParam.getSkuPrice().startsWith("_")){
              boolQueryBuilder.filter(QueryBuilders.rangeQuery("skuPrice").lt(s[0]));
          }
          boolQueryBuilder.filter(QueryBuilders.rangeQuery("skuPrice").gt(s[0]));
      }
      boolQueryBuilder.filter(QueryBuilders.rangeQuery("skuPrice").gt(s[0]).lt(s[1]));
  }
  //attr属性检索，注意该属性是nested
  //
  /*
       {
    "nested": {
      "path": "attrs",
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "attrs.attrId": {
                  "value": "12"
                }} } ]}}}  }
   */
  //和前端约定传输格式为：attrs=1_5寸:8寸&attrs=2_16G:8G
  if(searchParam.getAttrs()!=null&&searchParam.getAttrs().size()>0){
      //使用for循环遍历
      for (String s:searchParam.getAttrs()) {
          BoolQueryBuilder boolQueryBuilder1 = QueryBuilders.boolQuery();
          String[] s1 = s.split("_");
          String[] attrValues = s1[1].split(":");
          boolQueryBuilder1.must(QueryBuilders.termQuery("attrs.attrId",s1[0]));
          boolQueryBuilder1.must(QueryBuilders.termsQuery("attrs.value",attrValues));
          boolQueryBuilder.filter(QueryBuilders.nestedQuery("attrs",boolQueryBuilder1, ScoreMode.None));
      }
  }
  //封装所有检索
searchSourceBuilder.query(boolQueryBuilder);
10.2排序，分页，高亮
/**
 * 排序，分页，高亮
 * sort，from,size,highLight
 *   "sort": [
 *     {
 *       "skuPrice": {
 *         "order": "desc"
 *       }
 *     }
 *   ],
 *   "from": 0,
 *   "size": 5,
 *   "highlight": {
 *     "fields": {
 *       "skuTitle": {}
 *     },
 *     "pre_tags": "<b style='color:red'>",
 *     "post_tags": "</b>"
 *   }
 */
//排序条件：sort=price/salecount/hotscore_desc/asc
String sort = searchParam.getSort();
if(!StringUtils.isEmpty(sort)){
    String[] s = sort.split("_");
    searchSourceBuilder.sort(s[0],s[1].equalsIgnoreCase("ASC")?SortOrder.ASC:SortOrder.DESC);
}
//分页条件
//from=(pageNum-1)*size
//TODO 这个size我觉的应该也是需要前端传的，目前先后端写死，待后续优化
Integer pageNum = searchParam.getPageNum();
searchSourceBuilder.from((pageNum-1)* EsConstant.PRODUCT_PAGESIZE).size(EsConstant.PRODUCT_PAGESIZE);
//高亮
if(!StringUtils.isEmpty(searchParam.getKeyword())){
    searchSourceBuilder.highlighter().field("skuTitle").preTags("<b style='color:red'>").postTags("</b>");
}
10.3聚合aggs实现
 /*
 关于聚合agg部分
  */
 //一：品牌聚合
 TermsAggregationBuilder brandIdAgg = AggregationBuilders.terms("brandIdAgg");
 brandIdAgg.field("brandId").size(50);
 //品牌子聚合-brandNameAgg
 TermsAggregationBuilder brandNameAgg = AggregationBuilders.terms("brandNameAgg");
 brandNameAgg.field("brandName").size(1);
 brandIdAgg.subAggregation(brandNameAgg);
 //品牌子聚合-brandNameAgg
 TermsAggregationBuilder brandImgAgg = AggregationBuilders.terms("brandImgAgg");
 brandImgAgg.field("brandImg").size(1);
 brandIdAgg.subAggregation(brandImgAgg);
 searchSourceBuilder.aggregation(brandIdAgg);
 //二：分类聚合
 TermsAggregationBuilder catalogIdAgg = AggregationBuilders.terms("catalogIdAgg");
 catalogIdAgg.field("catalogId").size(50);
 TermsAggregationBuilder catalogNameAgg = AggregationBuilders.terms("catalogNameAgg");
 catalogNameAgg.field("catalogName").size(1);
 catalogIdAgg.subAggregation(catalogNameAgg);
 searchSourceBuilder.aggregation(catalogIdAgg);
 //三：属性聚合
 NestedAggregationBuilder nestedAttrsAgg = AggregationBuilders.nested("attrsAgg", "attrs");
 TermsAggregationBuilder attrIdAgg = AggregationBuilders.terms("attrIdAgg");
 attrIdAgg.field("attrs.attrId").size(50);
 TermsAggregationBuilder attrNameAgg = AggregationBuilders.terms("attrNameAgg");
 attrNameAgg.field("attrs.attrName").size(1);
 attrIdAgg.subAggregation(attrNameAgg);
 TermsAggregationBuilder attrValueAgg = AggregationBuilders.terms("attrValueAgg");
 attrValueAgg.field("attrs.attrValue").size(50);
 attrIdAgg.subAggregation(attrValueAgg);
 nestedAttrsAgg.subAggregation(attrIdAgg);
 searchSourceBuilder.aggregation(nestedAttrsAgg);
 searchRequest = new SearchRequest(new String[]{EsConstant.PRODUCT_INDEX},searchSourceBuilder);
// SearchRequest source = searchRequest.source(searchSourceBuilder);
 return searchRequest;
avatar
文章
阅读
粉丝