Elasticsearch QueryDSL 常用查询

2016年01月26日

主要是把Elasticsearch QueryDSL查询入门的部分进行了翻译总结，用以备查

(本文同步发在我的OSCHINA博客)

==> 注：’bank’ 为示例索引

==> 列出所有index: curl 'localhost:9200/_cat/indices?v

一、检出所有

1. 检出索引(index)为bank的所有文档(document)

   • REST request URI方式: curl 'localhost:9200/bank/_search?q=*&pretty

   • REST request body方式:

      curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
      {
        "query": { "match_all": {} }
      }'
             
     

2. 匹配所有并返回第一个文档

    curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
    {
      "query": { "match_all": {} },
      "size": 1
    }'
       
   

3. 匹配所有并返回第11到20

    curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
    {
      "query": { "match_all": {} },
      "from": 10,
      "size": 10
    }'
       
   

4. 匹配所有根据balance降序(DESC)排序并返回前10条数据

    curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
    {
      "query": { "match_all": {} },
      "sort": { "balance": { "order": "desc" } }
    }'
       
   

– ! 注意 !：上述中若size未指定默认为10

二、搜索

1. 返回account_number和balance这2个fields（_source里），相当于sql：SELECT account_number, balance FROM….

    curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
    {
      "query": { "match_all": {} },
      "_source": ["account_number", "balance"]
    }'
       
   

2. 返回account_number为20的，相当于sql的WHERE

    curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
    {
      "query": { "match": { "account_number": 20 } }
    }'
       
   

3. 返回address中包含’mill’或’lane’的

    curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
    {
       "query": { "match": { "address": "mill lane" } }
    }'
       
   

4. 返回address中包含’mill lane’的

5.  curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
    {
      "query": { "match_phrase": { "address": "mill lane" } }
    }'
       
   

   1. 返回address中包含’mill’和’lane’的

      curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
      {
      "query": {
       "bool": {
         "must": [
           { "match": { "address": "mill" } },
           { "match": { "address": "lane" } }
         ]
       }
      }
      }' 
          
      

   2. 返回address中包含’mill’或’lane’的

      curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
      {
      "query": {
       "bool": {
         "should": [
           { "match": { "address": "mill" } },
           { "match": { "address": "lane" } }
         ]
       }
      }
      }'
          
      

   3. 返回address中不包含’mill’和’lane’的

      curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
      {
      "query": {
       "bool": {
         "must_not": [
           { "match": { "address": "mill" } },
           { "match": { "address": "lane" } }
         ]
       }
      }
      }'
          
      

   4. 返回age是40但state不是’ID’的

      curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
      {
      "query": {
       "bool": {
         "must": [
           { "match": { "age": "40" } }
         ],
         "must_not": [
           { "match": { "state": "ID" } }
         ]
       }
      }
      }'
          
      

   三、过滤器

   1. 返回balance在20000和30000之间的(含)(balance大于等于20000，小于等于30000)

      curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
      {
      "query": {
       "filtered": {
         "query": { "match_all": {} },
         "filter": {
           "range": {
             "balance": {
               "gte": 20000,
               "lte": 30000
             }
           }
         }
       }
      }
      }'
          
      

   2. 按state分组并返回前10个(默认)，按分组COUNT降序(DESC)排

   • 相当于sql: SELECT COUNT() from bank GROUP BY state ORDER BY COUNT() DESC。

   • 「size=0」是设置不显示search hit

       curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
       {
         "size": 0,
         "aggs": {
           "group_by_state": {
             "terms": {
               "field": "state"
             }
           }
         }
       }'
             
     

   1. 按state分组并计算(组)平均balance(默认返回前10个按state的COUNT的降序(DESC)排)

      curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
      {
      "size": 0,
      "aggs": {
       "group_by_state": {
         "terms": {
           "field": "state"
         },
         "aggs": {
           "average_balance": {
             "avg": {
               "field": "balance"
             }
           }
         }
       }
      }
      }'
          
      

   2. 同上一个，但，本例按balance平均数的降序排

      curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
      {
      "size": 0,
      "aggs": {
       "group_by_state": {
         "terms": {
           "field": "state",
           "order": {
             "average_balance": "desc"
           }
         },
         "aggs": {
           "average_balance": {
             "avg": {
               "field": "balance"
             }
           }
         }
       }
      }
      }'
          
      

   3. 按年龄(age)20-29, 30-39, 40-49区间分组(GROUP BY), 每个区间按性别(gender)分组， 计算平均(balance)。按句话说，就是计算不同年龄区间的男女平均余额。

      curl -XPOST 'localhost:9200/bank/_search?pretty' -d '
      {
      "size": 0,
      "aggs": {
       "group_by_age": {
         "range": {
           "field": "age",
           "ranges": [
             {
               "from": 20,
               "to": 30
             },
             {
               "from": 30,
               "to": 40
             },
             {
               "from": 40,
               "to": 50
             }
           ]
         },
         "aggs": {
           "group_by_gender": {
             "terms": {
               "field": "gender"
             },
             "aggs": {
               "average_balance": {
                 "avg": {
                   "field": "balance"
                 }
               }
             }
           }
         }
       }
      }
      }'