查询某个字段不等于空字符串,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>() {
    @Override
    public 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;