DSL的诞生 | 复杂sql转成Elasticsearch DSL深入详解_dsl sql对照

问题如下：

where (position=ES or work=ES or content=ES) and academic=本科 and （city=北京 or city=深圳）
 
 
1

怎么构建ES的查询条件？

我的问题拆解与实现如下：

1、sql语句转成DSL有哪些方法？

方案一：借助工具 NLP团体开发的Elasticsearch-sql; 
2.X安装过，5.X没有再安装。 
方案二：借助工具ElasticHQ的自动转换模块： 
 
方案一、方案二和Github上其他语言开发的sql转DSL工具对简单的sql生成的DSL相对准确，但对于复杂的sql生成的不一定精确。（如上所示）

方案三：徒手生成。

2、如何根据复杂的sql语句生成ES的DSL查询语句呢？

步骤1：拆解

where (position=ES or work=ES or content=ES) and academic=本科 and （city=北京 or city=深圳）

这个sql语句由几部分组成呢？ 
以and作为拆分，共分为3部分： 
三个部分sql用and衔接，转换为DSL对应最外层must；

第一部分： position=ES or work=ES or content=ES 
三个子条件sql用or衔接，转换DSL对应should；

第二部分： academic=本科 
单一条件转换为DSL对应term精确匹配；

第三部分： city=北京 or city=深圳 
city的两个or语句转换为DSL对应terms多词精确匹配。

上面的sql都用的=号，假定不需要分词，我们统一采用termquery和termsquery实现。 
引申： 
如果需要分词，更换为matchquery或者match_parsequery即可。 
如果需要模糊匹配，更换为wildcardquery接口。

步骤2：套bool多条件检索DSL模板

复杂bool多条件检索DSL模板： 
包含了：查询/检索、聚合、排序、指定字段输出、输出起点、输出多少等信息。

{
  "query": {
  "bool": {
  "must": [],
  "must_not": [],
  "should": []
  }
  },
  "aggs": {
  "my_agg": {
  "terms": {
  "field": "user",
  "size": 10
  }
  }
  },
  "highlight": {
  "pre_tags": [
  "<em>"
  ],
  "post_tags": [
  "</em>"
  ],
  "fields": {
  "body": {
  "number_of_fragments": 1,
  "fragment_size": 20
  },
  "title": {}
  }
  },
  "size": 20,
  "from": 100,
  "_source": [
  "title",
  "id"
  ],
  "sort": [
  {
  "_id": {
  "order": "desc"
  }
  }
  ]
}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

简单bool多条件查询DSL模板： 
只包含查询。

{
  "query": {
  "bool": {
  "must": [],
  "must_not": [],
  "should": []
  }
  }
}
 
 
1
2
3
4
5
6
7
8
9

以上根据我们的sql特点，简单模板即能满足要求。

步骤3：构造生成DSL

根据，步骤1、步骤2，可以构思出根据sql转换后的DSL应该： 
1）最外层bool 
2）第二层：must 三个并行条件 
3）第三层：各自的匹配条件。（存在bool嵌套bool的情况）

3、动动手，验证下。

3.1 创建索引（自动生成mapping）

put test_index_01
 
 
1

3.2 提交数据

post test_index_01/test_type_01/1
{
  "no":"1",
  "city":"北京",
  "academic":"专科",
  "content":"ES",
  "position":"ES",
  "work":"ES"
}
post test_index_01/test_type_01/2
{
  "no":"2",
  "city":"天津",
  "academic":"本科",
  "content":"ES",
  "position":"ES",
  "work":"ES"
}
post test_index_01/test_type_01/3
{
  "no":"3",
  "city":"深圳",
  "academic":"本科",
  "content":"ES",
  "position":"ES2",
  "work":"ES3"
}
post test_index_01/test_type_01/4
{
  "no":"4",
  "city":"北京",
  "academic":"本科",
  "content":"ES1",
  "position":"ES2",
  "work":"ES"
}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

插入后ES-head插件控制台查询结果： 

3.3 完成检索

post test_index_01/_search
{
  "query": {
  "bool": {
  "must": [
  {
  "terms": {
  "city.keyword": [
  "北京",
  "深圳"
  ]
  }
  },
  {
  "term": {
  "academic.keyword": "本科"
  }
  },
  {
  "bool": {
  "should": [
  {
  "term": {
  "content.keyword": "ES"
  }
  },
  {
  "term": {
  "position.keyword": "ES"
  }
  },
  {
  "term": {
  "work.keyword": "ES"
  }
  }
  ]
  }
  }
  ]
  }
  },
  "size": 10,
  "from": 0
}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

注意： 
没有做分词，做的精确匹配，所以加了”.keyword”。

3.4 返回结果

{
  "took": 1,
  "timed_out": false,
  "_shards": {
  "total": 5,
  "successful": 5,
  "failed": 0
  },
  "hits": {
  "total": 2,
  "max_score": 1.0577903,
  "hits": [
  {
  "_index": "test_index_01",
  "_type": "test_type_01",
  "_id": "4",
  "_score": 1.0577903,
  "_source": {
  "no": "4",
  "city": "北京",
  "academic": "本科",
  "content": "ES1",
  "position": "ES2",
  "work": "ES"
  }
  },
  {
  "_index": "test_index_01",
  "_type": "test_type_01",
  "_id": "3",
  "_score": 0.8630463,
  "_source": {
  "no": "3",
  "city": "深圳",
  "academic": "本科",
  "content": "ES",
  "position": "ES2",
  "work": "ES3"
  }
  }
  ]
  }
}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

4、小结

实践是检验真理的唯一标准！ 
如有不同意见，欢迎拍砖探讨！