将Mysql查询转换为Elastic Search [英] Convert Mysql Query to Elastic Search

问题描述

关于我先前的问题 ElasticSearch查询，其中给出了映射和doc示例我想将此MySql查询转换为Elasticsearch.这是mysql查询

in regards to my earlier question ElasticSearch Query where mapping and doc sample is given I want to convert this MySql query to Elasticsearch. Here is the mysql query

Select * from us_data where phone!=0 AND city_code IN ('Homestead','Hialeah','Key Biscayne','Miami Beach','Miami','North Miami Beach','Ochopee','Opa Locka') AND state_code='FL' AND (name like '%appliance%' or city_code like '%appliance%' or address like '%appliance%' or phone like '%appliance%')

期待您的帮助

推荐答案

下面的查询应该会对您有所帮助.请注意，在您的映射中，您有 contact_no ，而在上述查询中，您提到了 phone .

Below query should help you. Note that in your mapping you have contact_no while in the above query you've mentioned phone.

尽管如此，我还是使用了 contact_no ，以便它与映射保持一致.

Nevertheless, I've made use of contact_no so that it sticks with the mapping.

POST <your_index_name>/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "city_code.keyword": [
              "Homestead",
              "Hialeah",
              "Key Biscayne",
              "Miami Beach",
              "Miami",
              "North Miami Beach",
              "Ochopee",
              "Opa Locka"
            ]
          }
        },
        {
          "term": {
            "state_code.keyword": "FL"
          }
        },
        {
          "multi_match": {
            "query": "*appliance*",
            "fields": ["name","city_code","address","contact_no"]
          }
        }
      ],
      "must_not": [
        {
          "term": {
            "contact_no.keyword": "0"
          }
        }
      ]
    }
  }
}

我使用了条款查询，字词查询和多次匹配查询.

请注意，我在 keyword 字段上使用了Term Query/Terms Query，从sql-query看来，您需要完全匹配，而我使用的是 multi-match在 text 字段上.

Note that I've used Term Query/Terms Query on the keyword fields as from the sql-query it appears you want an exact match, while I've used multi-match on the text fields.

让我知道这是否有帮助！

Let me know if this helps!

这篇关于将Mysql查询转换为Elastic Search的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！