mysql - MySQL查询到ElasticSearch

Question

我正在尝试将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
    }
  }

Answer 1

您正在使事情变得复杂，并试图在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
                }
            }
        ]