526互联

Elasticsearch之Nested Query nestedQuery查询数组

发布时间 2023-06-05 22:03:19作者: oktokeep

es是通过符合条件的json记录找出来，本身并不是将数据中的记录filter过滤。es nestedQuery不是过滤的结果，是匹配的这条es记录，所以数组中的其他的记录也会查询出来
1.方法1：可以在程序中对数组中的记录过滤。
2.方法2：在数据初始化的时候，将数据严格查询符合条件的才初始化。

需求背景：收益的记录，根据订单号和会员号来初始化，理论上而不是仅仅根据订单号来查询收益列表，有一种情况是一个订单号存在多个不同的会员号的收益。

1.order_info_v1/_mapping GET

"settleIncomeExamineList": {
	"type": "nested",
	"properties": {
		"amt": {
			"type": "long"
		},
		"detail": {
			"type": "text",
			"fields": {
				"keyword": {
					"type": "keyword",
					"ignore_above": 256
				}
			}
		},
		"id": {
			"type": "long"
		},
		"memNo": {
			"type": "text",
			"fields": {
				"keyword": {
					"type": "keyword",
					"ignore_above": 256
				}
			}
		},
		"opName": {
			"type": "text",
			"fields": {
				"keyword": {
					"type": "keyword",
					"ignore_above": 256
				}
			}
		},
		"orderNo": {
			"type": "text",
			"fields": {
				"keyword": {
					"type": "keyword",
					"ignore_above": 256
				}
			}
		},
		"remark": {
			"type": "text",
			"fields": {
				"keyword": {
					"type": "keyword",
					"ignore_above": 256
				}
			}
		},
		"status": {
			"type": "long"
		},
		"time": {
			"type": "date"
		},
		"type": {
			"type": "long"
		}
	}
},

2.data格式  

"settleIncomeExamineList": [{
		"orderNo": "997393306032abc",
		"memNo": "159057421",
		"amt": -169,
		"remark": "违约金罚金",
		"id": 1194,
		"detail": "违约金罚金",
		"type": 1,
		"status": 1
	},
	{
		"orderNo": "997393306032abc",
		"memNo": "690182074",
		"amt": 352,
		"remark": "结算收益",
		"id": 1195,
		"detail": "结算收益",
		"type": 1,
		"status": 1
	}
],

3. rest api验证

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "settleIncomeExamineList", 
"score_mode":"max",
            "query": {
              "bool": {
                "must": [ 
				  {
                    "term": {
                      "settleIncomeExamineList.memNo": "159057421"
                    }
                  },
				  {
                    "term": {
                      "settleIncomeExamineList.orderNo": "997393306032abc"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
}}}



{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "settleIncomeExamineList", 
"score_mode":"max",
            "query": {
              "bool": {
                "must": [ 
				  {
                    "term": {
                      "settleIncomeExamineList.memNo": "690182074"
                    }
                  },
				  {
                    "term": {
                      "settleIncomeExamineList.orderNo": "997393306032abc"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
}}}

反例：随便更改会员号，则搜索不出来。
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "settleIncomeExamineList", 
"score_mode":"max",
            "query": {
              "bool": {
                "must": [ 
				  {
                    "term": {
                      "settleIncomeExamineList.memNo": "111222333"
                    }
                  },
				  {
                    "term": {
                      "settleIncomeExamineList.orderNo": "997393306032abc"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
}}}

4.Java代码

boolQueryBuilder.must(QueryBuilders.nestedQuery("settleIncomeExamineList", QueryBuilders.termQuery("settleIncomeExamineList.orderNo.keyword", reqVO.getOrderNo()), ScoreMode.None));
    boolQueryBuilder.must(QueryBuilders.nestedQuery("settleIncomeExamineList", QueryBuilders.termQuery("settleIncomeExamineList.type", 3), ScoreMode.None));   
    boolQueryBuilder.must(QueryBuilders.nestedQuery("settleIncomeExamineList",QueryBuilders.existsQuery("settleIncomeExamineList"), ScoreMode.None));    

    int[] statusArray = reqVO.getStatusList().stream().mapToInt(Integer::intValue).toArray();
    boolQueryBuilder.must(QueryBuilders.nestedQuery("settleIncomeExamineList", QueryBuilders.termsQuery("settleIncomeExamineList.status", statusArray),ScoreMode.None));