MySQL查询到ElasticSearch [英] Mysql query to ElasticSearch
问题描述
我正在尝试将MYSQL查询转换为Elasticsearch.该查询包括在不同字段上的多个条件.让我解释一下我要达到的目标.我的MySQL查询是
I am trying to convert my MYSQL query to Elasticsearch. The query includes multiple conditions on different fields. Let me explain what i am trying to achieve. My Mysql query is
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中复制此查询.我的尝试是
how this query can be replicated in elasticsearch. My attempt is
$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复制此查询，但答案仍然相同.
But this doesn't return anything. I am using Elasticsearch 7.6 and trying to replicate this query with curl on Kibana but the answer is still the same.
期待获得帮助
根据要求，索引的映射为
As requested the mapping of the index is
{
"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概念，在这种情况下，您需要正确定义索引映射(字段数据类型及其分析器根据搜索要求)并相应地构建您的查询.
You are complicating the things and trying to fit MySQL concept in Elasticsearch, In this case, you need to properly define your index mapping(fields data types and their analyzer based on the search requirements) and accordingly build your queries.
我已经获取了您的示例，并且没有更改索引映射和示例文档，但是更改了搜索查询以显示如何处理现有数据和需求(可能在所有情况下均不起作用，但您有所了解)它可以带来搜索.
I've taken your sample and didn't change your index mapping and sample document, but changed the search query to show, how with your existing data and requirement(may not work in all cases, but you gets an idea) it can bring the search.
{
"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
}
}
]
这篇关于MySQL查询到ElasticSearch的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
