es 实现字段之间进行比对

落日映苍穹つ 2023-06-23 11:59 16阅读 0赞

sql 例如:

select \* from user t where t.count = t.num;

es 查询语句如下:

{
      "query": {
        "bool": {
          "must": {
            "script": {
              "script": {
                "source": "doc['fieldA'].value >= doc['fieldB'].value"
              }
            }
          }
        }
      }
    }

示例：

{
    	"from": 0,
    	"size": 100,
    	"query": {
    		"bool": {
    			"filter": {
    				"script": {
    					"script": {
    						"inline": "doc['count'].value - doc['num'].value == 0",
    						"lang": "painless"
    					},
    					"boost": 1
    				}
    			}
    		}
    	}
    }

javaApi 如下:

Map<String, Object> params = new HashMap<>();
            Script script = new Script(ScriptType.INLINE,"painless","doc['count'].value - doc['num'].value == 0",params);
            ScriptQueryBuilder scriptQueryBuilder = new ScriptQueryBuilder(script);
            StringBuffer queryString = new StringBuffer();
           //   queryString.append("goods_id:18506");
            QueryStringQueryBuilder queryStringQueryBuilder = new QueryStringQueryBuilder(queryString.toString());
            BoolQueryBuilder booleanQueryBuilder = QueryBuilders.boolQuery();
            booleanQueryBuilder.must(scriptQueryBuilder);
            if (queryString.length() > 0){
                booleanQueryBuilder.must(queryStringQueryBuilder);
            }
            System.out.println("查询条件:" + booleanQueryBuilder);
            SearchResponse searchResponse = getClient().prepareSearch("goods_recommend")
                    .setTypes("tags")
                    .setQuery(booleanQueryBuilder)
                    .get();
            SearchHit[] hits = searchResponse.getHits().getHits();
            List list = new ArrayList();
            for (int i = 0; i < hits.length; i++) {
                System.out.println(JSON.toJSON(hits[i]));
            //            list.add(hits[i].getId());
            }

### ES中Date型字段不能直接用>,<,+,-进行比较和计算，会报错。 ###

可以先把Date类型取得getMillis()，取得时间戳，然后就可以进行比较了。

使用TransportClient代码如下,param是传入的参数，可以在script中使用

Map<String, Object> params = Maps.newHashMap();
            params.put("datesub", 5);
            QueryBuilder qb = QueryBuilders.boolQuery()
                    .must(QueryBuilders.scriptQuery(
                            new Script(Script.DEFAULT_SCRIPT_TYPE, 
                                    Script.DEFAULT_SCRIPT_LANG, 
                                    "doc['updated_time'].value.getMillis() - doc['create_time'].value.getMillis() < params.datesub", 
                                    params)));

script：

{
    	"from": 0,
    	"size": 100,
    	"query": {
    		"bool": {
    			"filter": {
    				"script": {
    					"script": {
    						"inline": "doc['updated_time'].value.getMillis() - doc['updated_time'].value.getMillis() == 0",
    						"lang": "painless"
    					},
    					"boost": 1
    				}
    			}
    		}
    	}
    }

参照：

[https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html][https_www.elastic.co_guide_en_elasticsearch_reference_current_index.html]

[https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting-using.html][https_www.elastic.co_guide_en_elasticsearch_reference_current_modules-scripting-using.html]

[https_www.elastic.co_guide_en_elasticsearch_reference_current_index.html]: https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html
[https_www.elastic.co_guide_en_elasticsearch_reference_current_modules-scripting-using.html]: https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting-using.html