美文网首页
常用mysql语句转elasticsearch查询语句

常用mysql语句转elasticsearch查询语句

作者: Jin110 | 来源:发表于2019-03-15 13:59 被阅读0次

    转载:https://blog.csdn.net/zfy1355/article/details/82968118

    1. 多个查询条件 bool(must、should、must_not)
    select * from index_person where name = 'jeck' or sex = 'male' order by age,sex
    {
        "query":{
            "bool":{
                "should":{
                    {"match":{"name": "jeck"}},
                    {"match": {"sex": "male"}},
                }
            }       
        },
        "sort":[{"age":{"order":"asc"},
                {"sex":{"order":"desc"}}]
    }
    
    
    1. like查询 wildcard
    select * from index_person where name like '%jeck%'
    
    {
        "query":{
            "bool":{
                "must":{
                    "wildcard":{
                        "name":"*jeck*"
                    }
                }
            }       
        }
    }
    
    
    1. 区间查询 range
    select * from index_person where name = 'jeck' and sex = 'male' and (age<30 and age>40)
    
    {
     "query": {
        "bool": {
            "must": [
                {"match":{"name": "jeck"}},
                {"match": {"sex": "male"}},
                {"range" : {"age" : {"gt" : 40,"lt":30}}}
            ]
        }
      }
    }
    
    
    1. 根据bucket提取汇总数,如果对汇总数继续分组,可以在group_by_sex后继续添加aggs结构
    select count(sex) from index_person where age>30 group by sex;
    
    {
      "size":10,
      "query":{
        "bool":{
          "must":[{"range" : {"age" : {"gt" : 30}}}]
        }
      },
      "aggs":{
        "group_by_sex":{
          "terms":{
            "field":"sex"
          }
        }
      }
    }
    
    
    1. 分组调用内置函数,(avg,sum)
    select average(age) from index_person  group by sex 
    {
      "size":10,
      "aggs":{
        "avg_by_age":{
          "avg":{
            "field":"sex"
          }
        }
      }
    }
    

    </article>

    相关文章

      网友评论

          本文标题:常用mysql语句转elasticsearch查询语句

          本文链接:https://www.haomeiwen.com/subject/slblmqtx.html