我正在尝试将MYSQL查询转换为Elasticsearch。该查询包括在不同字段上的多个条件。让我解释一下我要达到的目标。我的MySQL查询是
Select * from data_fl where city IN 'miami,miamibeach,etc' AND phone!=0 AND (name like '%abc%' OR address like '%abc%' OR zip_code like '%abc%' OR phone Like '%abc')
如何在Elasticsearch中复制此查询。我的尝试是
$params = [ 'index'=>'us_data_'.strtolower($state_code), 'body' => [ 'query' => [ 'bool'=>[ 'filter'=>[ 'term'=>['city_code'=>$city_name] ], 'should' => [ 'query_string'=>[ 'query'=>"*".$service."*", 'fields'=>['name','contact_no','zip_code','city_code'], ] ] ] ] ] ];
但这不会返回任何内容。我正在使用Elasticsearch 7.6并尝试在Kibana上使用curl复制此查询，但答案仍然相同。
期待帮助
根据要求，索引的映射是
{ "mapping": { "_doc": { "properties": { "@timestamp": { "type": "date" }, "@version": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "address": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "city_code": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "contact_no": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "date_added": { "type": "date" }, "date_updated": { "type": "date" }, "featured": { "type": "long" }, "id": { "type": "long" }, "location_id": { "type": "long" }, "main_cate": { "type": "long" }, "name": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "slug": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "source": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "state_code": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "status": { "type": "long" }, "zip_code": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } } } } }
我接受的文件是
"hits" : { "total" : { "value" : 10000, "relation" : "gte" }, "max_score" : 1.0, "hits" : [ { "_index" : "us_data_al", "_type" : "_doc", "_id" : "8kmR1HABkLcaz3xayZOg", "_score" : 1.0, "_source" : { "promotion" : null, "image" : null, "name" : "Port City Realty", "city_code" : "Mobile", "services" : null, "promotion_exp_date" : null, "tuesdayopen" : null, "tuesdayclose" : null, "wednesdayopen" : null, "thursdayclose" : null, "@timestamp" : "2020-03-13T15:44:45.330Z", "date_updated" : "2020-03-06T00:00:00.000Z", "mondayopen" : null, "contact_no" : "2516891228", "id" : 1941, "fridayclose" : null, "featured" : 0, "main_cate" : 1, "wednesdayclose" : null, "sundayopen" : null, "state_code" : "AL", "video" : null, "address" : "4826 Whispering Oaks Lane", "user_id" : null, "slug" : "2516891228-port-city-realty-mobile-al-36695", "timezone" : null, "source" : "USA Business", "description" : null, "fridayopen" : null, "price" : null, "saturdayopen" : null, "saturdayclose" : null, "date_added" : "2020-03-05T19:00:00.000Z", "thursdayopen" : null, "@version" : "1", "status" : 1, "mondayclose" : null, "zip_code" : "36695", "private_contact" : null, "location_id" : 0, "sundayclose" : null } }
您正在使事情变得复杂，并试图在Elasticsearch中适应MySQL概念。在这种情况下，您需要正确定义索引映射（根据搜索要求来字段数据类型及其分析器），并相应地构建查询。
我已经获取了您的示例，并且没有更改索引映射和示例文档，但是更改了搜索查询以显示如何处理现有数据和需求（可能无法在所有情况下都有效，但是您有所了解）搜索。
{ "query": { "multi_match": { --> note and read about multi_match query "query": "36695", "fields": [ "address", "city_code", --> add more fields if you need to be "zip_code", "contact_no" ] } } }
搜索结果将带您的示例文档：
"hits": [ { "_index": "so_mysql_dsl", "_type": "_doc", "_id": "1", "_score": 0.2876821, "_source": { "promotion": null, "image": null, "name": "Port City Realty", "city_code": "Mobile", "services": null, "promotion_exp_date": null, "tuesdayopen": null, "tuesdayclose": null, "wednesdayopen": null, "thursdayclose": null, "@timestamp": "2020-03-13T15:44:45.330Z", "date_updated": "2020-03-06T00:00:00.000Z", "mondayopen": null, "contact_no": "2516891228", "id": 1941, "fridayclose": null, "featured": 0, "main_cate": 1, "wednesdayclose": null, "sundayopen": null, "state_code": "AL", "video": null, "address": "4826 Whispering Oaks Lane", "user_id": null, "slug": "2516891228-port-city-realty-mobile-al-36695", "timezone": null, "source": "USA Business", "description": null, "fridayopen": null, "price": null, "saturdayopen": null, "saturdayclose": null, "date_added": "2020-03-05T19:00:00.000Z", "thursdayopen": null, "@version": "1", "status": 1, "mondayclose": null, "zip_code": "36695", "private_contact": null, "location_id": 0, "sundayclose": null } } ]
