ES和SQL查询对比 多字段组合条件查询

本意：

        只返回河南省25岁和河北省26岁的

SQL:

select * from tableName where
   (province = '河南' and age = 25) 
or (province = '河北' and age = 26)

对应的ES：

{
	"from": 0,
	"size": 10,
	"query": {
		"bool": {
			"filter": [
				{
					"bool": {
						"should": [
							{
								"bool": {
									"must": [
										{
											"term": {
												"province": {
													"value": "河南",
													"boost": 1.0
												}
											}
										},
										{
											"term": {
												"age": {
													"value": 25,
													"boost": 1.0
												}
											}
										}
									],
									"adjust_pure_negative": true,
									"boost": 1.0
								}
							},
							{
								"bool": {
									"must": [
										{
											"term": {
												"province": {
													"value": "河北",
													"boost": 1.0
												}
											}
										},
										{
											"term": {
												"age": {
													"value": 26,
													"boost": 1.0
												}
											}
										}
									],
									"adjust_pure_negative": true,
									"boost": 1.0
								}
							}
						],
						"adjust_pure_negative": true,
						"boost": 1.0
					}
				}
			],
			"adjust_pure_negative": true,
			"boost": 1.0
		}
	}
}

本意：

        只返回河南22,23,24,25,和河北26,27,28,29岁的

SQL：

select * from tableName where
   (province = '河南' and age in (22, 23, 24, 25)) 
or (province = '河北' and age in (26, 27, 28, 29))

对应的ES:

{
	"from": 0,
	"size": 10,
	"query": {
		"bool": {
			"filter": [
				{
					"bool": {
						"should": [
							{
								"bool": {
									"must": [
										{
											"term": {
												"province": {
													"value": "河南",
													"boost": 1.0
												}
											}
										},
										{
											"terms": {
												"age": [
													22,
													23,
													24,
													25
												],
												"boost": 1.0
											}
										}
									],
									"adjust_pure_negative": true,
									"boost": 1.0
								}
							},
							{
								"bool": {
									"must": [
										{
											"term": {
												"province": {
													"value": "河北",
													"boost": 1.0
												}
											}
										},
										{
											"terms": {
												"age": [
													26,
													27,
													28,
													29
												],
												"boost": 1.0
											}
										}
									],
									"adjust_pure_negative": true,
									"boost": 1.0
								}
							}
						],
						"adjust_pure_negative": true,
						"boost": 1.0
					}
				}
			],
			"adjust_pure_negative": true,
			"boost": 1.0
		}
	}
}

解释：

我们先看外层的filter，filter其实相当于SQL中的where的意思，对结果进行过滤，

should节点里面的子节点，意味着满足任一即可，所以should也就是SQL中的or的意思，我们剖析should里面的子节点，

我们可以看到should里面是两个bool的子节点，然后单个bool子节点分析，单个bool节点下面是must节点，

must对应的是个数组，就意味着这个数组里面的条件都要满足，也就是must相当于SQL里面的and的意思，

must_not相当于SQL里面的!代表非的意思

term只能匹配一个值，terms可以匹配多个值，数据满足任一值即可，相当于SQL中的in的意思，

这样你就能明白上面语句的意思了。

本意：

        获取除了河南25岁和河北26岁的所有数据

SQL

select * from tableName where
   !(province = '河南' and age = 25) 
or !(province = '河北' and age = 26)

 ES

{
	"from": 0,
	"size": 10,
	"query": {
		"bool": {
			"filter": [
				{
					"bool": {
						"must_not": [
							{
								"bool": {
									"must": [
										{
											"term": {
												"province": {
													"value": "河南",
													"boost": 1.0
												}
											}
										},
										{
											"term": {
												"age": {
													"value": 25,
													"boost": 1.0
												}
											}
										}
									],
									"adjust_pure_negative": true,
									"boost": 1.0
								}
							},
							{
								"bool": {
									"must": [
										{
											"term": {
												"province": {
													"value": "河北",
													"boost": 1.0
												}
											}
										},
										{
											"term": {
												"age": {
													"value": 26,
													"boost": 1.0
												}
											}
										}
									],
									"adjust_pure_negative": true,
									"boost": 1.0
								}
							}
						],
						"adjust_pure_negative": true,
						"boost": 1.0
					}
				}
			],
			"adjust_pure_negative": true,
			"boost": 1.0
		}
	}
}

本意

           只返回排除河南22,23,24,25,和 河北26,27,28,29岁的数据

SQL

select * from tableName where
   !(province = '河南' and age in (22, 23, 24, 25)) 
or !(province = '河北' and age in (26, 27, 28, 29))

ES

{
	"from": 0,
	"size": 10,
	"query": {
		"bool": {
			"filter": [
				{
					"bool": {
						"must_not": [
							{
								"bool": {
									"must": [
										{
											"term": {
												"province": {
													"value": "河南",
													"boost": 1.0
												}
											}
										},
										{
											"terms": {
												"age": [
													22,
													23,
													24,
													25
												],
												"boost": 1.0
											}
										}
									],
									"adjust_pure_negative": true,
									"boost": 1.0
								}
							},
							{
								"bool": {
									"must": [
										{
											"term": {
												"province": {
													"value": "河北",
													"boost": 1.0
												}
											}
										},
										{
											"terms": {
												"age": [
													26,
													27,
													28,
													29
												],
												"boost": 1.0
											}
										}
									],
									"adjust_pure_negative": true,
									"boost": 1.0
								}
							}
						],
						"adjust_pure_negative": true,
						"boost": 1.0
					}
				}
			],
			"adjust_pure_negative": true,
			"boost": 1.0
		}
	}
}