教你快速从SQL过度到Elasticsearch的DSL查询

前言

Elasticsearch太强大了,强大到跟python一样,一种查询能好几种语法。其实我们用到的可能只是其中的一部分,比如:全文搜索。
我们一般是会将mysql的部分字段导入到es,再查询出相应的ID,再根据这些ID去数据库找出来。

问题来了:数据导入到es后,很多人都要面对这个es的json查询语法,也叫DSL,如下

于是一堆新词来了,比如:filter、match、multi_match、query、term、range,容易让没学过的人抵触。

如果正常开发业务的程序员,只关心原先怎么用sql查询出来的数据,在es中查询出来。
sql查询定位,一般常用的是:=、!=、>、<、and、or、in、between等等。

举个例子,原先sql查询一商品是这样的

SELECT * FROM goods WHERE spu_id = "wp123" OR ( spu_id = "wp345" AND min_price = 30 ) 

对应到es是

{
  "query": {
    "bool": {
      "should": [
        {
          "term": {
            "spu_id": "wp123"
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "spu_id": "wp345"
                }
              },
              {
                "term": {
                  "min_price": 30
                }
              }
            ]
          }
        }
      ]
    }
  }
}

sql和dsl是有一定对应关系的,下面把一些常用的总结下,让不熟悉es的童鞋能丝滑能从sql过度

以下内容由chenqionghe倾情提供,祝您es使用愉快

bool-相当于一个括号

用bool包含起来的{},相当用()包含了一个复合查询语句,如上边的

{
  "bool": {
    "must": [
      {
        "term": {
          "spu_id": "wp345"
        }
      },
      {
        "term": {
          "min_price": 30
        }
      }
    ]
  }
}

相当于

看到没有就是这么简单

should-相当于or

must-相当于and

must_not-相当于 ! and

这个就相当于and取反了,

例如:

SELECT  *  FROM goods WHERE !(shop_id =79)

相当于

{
  "query": {
    "bool": {
      "must_not": [
        {
          "term": {
            "shop_id": "79"
          }
        }
      ]
    }
  }
}

term-相当于=

例如

SELECT * FROM goods WHERE shop_id =79

相当于

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {"shop_id": "79"}
        }
      ]
    }
  }
}

terms-相当于in

例如

SELECT * FROM goods WHERE shop_id in (79,80,81)

相当于

{
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "shop_id": [79, 80, 81]
          }
        }
      ]
    }
  }
}

range-相当于between

例如

SELECT * FROM goods WHERE id between 1000 and 10005

相当于

{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "id": {
              "gte": 1000,
              "lte": 10005
            }
          }
        }
      ]
    }
  }
}

exist相当于is not null

例如

SELECT * FROM goods WHERE id is not null

相当于

{
  "query": {
    "bool": {
      "must_not": [
        {
          "exists": {
            "field": "id"
          }
        }
      ]
    }
  }
}

match-类似match...against

这个match就相当于mysql的全文索引,关于mysql的全文索引,可以看一下这篇文章:从零开始学习MySQL全文索引

举个查询的例子,我要搜索包含 "海南 2018"的词,如下
{
	"query": {
		"match": {
			"name": "海南 2018"
		}
	}
}

这相当于把所有的“海南”和“2018”记录找出来了,他们是一个or的关系。如果想同时匹配怎么办呢?
可以这样,指定一个operator,默认是用的"or",可以改成这样

{
  "query": {
    "match": {
      "name": {
        "query": "海南 2018",
        "operator": "and"
      }
    }
  }
}

includes-相当于select

比如

SELECT id,name FROM goods WHERE id = 1765

相当于

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "id": 1765
          }
        }
      ]
    }
  },
  "_source":{"includes":["id","name"]}
}

sort-相当于order by

比如

SELECT * FROM goods ORDER BY id DESC 

相当于

{
	"sort": [
	  {
	    "id": {
	      "order": "desc"
	    }
	  }
	]
}



from size-相当于limit

例如

SELECT * FROM goods ORDER BY id DESC LIMIT 5,2;

相当于

{
 "from": 5,
 "size": 2
}

到这里,差不多就已经可以丝滑地从sql过度到es的dsl了

一些常见问题

match和term的区别

match在匹配时会对所查找的关键词进行分词,然后按分词匹配查找,而term会直接对关键词进行查找。一般模糊查找的时候,多用match

query和filter的区别

filter:只查询出搜索条件的数据,不计算相关度分数
query:查询出搜索条件的数据,并计算相关度分数,按照分数进行倒序排序
filter比query性能好,两者可以一起使用

filtered和filter区别

filtered是比较老的的版本的语法。现在已经被bool替代,推荐使用bool。
老版本写法

{
  "query": {
    "filtered": {
      "query": {
        "match": {
          "text": "quick brown fox"
        }
      },
      "filter": {
        "term": {
          "status": "published"
        }
      }
    }
  }
}

新版本写法

{
  "query": {
    "bool": {
      "must": {
        "match": {
          "text": "quick brown fox"
        }
      },
      "filter": {
        "term": {
          "status": "published"
        }
      }
    }
  }
}

filter两种用法
嵌套在bool下

{
  "query": {
    "bool": {
      "must": {
        "term": {
          "term": {
            "title": "kitchen3"
          }
        }
      },
      "filter": {
        "term": {
          "price": 1000
        }
      }
    }
  }
}

在根目录下使用

{
  "query": {
    "term": {
      "title": "kitchen3"
    }
  },
  "filter": {
    "term": {
      "price": 1000
    }
  }
}

term和terms的区别

term相当于where =
terms相当于 where in (xx,xx,xx)
如果想要=于多次,得用多个term,而不是terms

如何高亮关键词

这里需要用到
比如,我们查询包含“海南”和“面试”的词,match指定"海南 面试",highlight指定字段和要包含的标签

GET /goods-search-v20210511/_search
{
  "query": {
    "match": {
      "name": "海南 面试"
    }
  },
  "highlight": {
    "fields": {
      "name": {
        "pre_tags": [
        "<cheniqonghe>"
      ], 
      "post_tags": [
        "</cheniqonghe>"
      ]
      }
    }
  },
  "_source":{"includes":["id","name"]}
} 

查询结果如下

可以看到es已经将关键词用指定的标签包起来了

有没有sql生成dsl的工具

有,找到一个:在线sql转dsl
但是这种工具只能是作为辅助,不能完全靠它

  1. 傻瓜式的生成不一定是最优的
  2. sql有局限性,比如没有高亮、嵌套查询等等。

我们可以生成,再自己优化成最终的json

posted @ 2021-08-17 17:47  雪山飞猪  阅读(721)  评论(2编辑  收藏  举报