我想用eleasticsearch实现下面的TSQL查询
declare @searchstring nvarchar (max)
set @searchstring = 'tn-241'
set @searchstring = replace(replace('%'+@searchstring+'%', '-', ''), ' ', '')
SELECT *
FROM [dbo].[Product]
where
replace(replace(shortdescription, '-', ''), ' ', '') like @searchstring or
replace(replace(name, '-', ''), ' ', '') like @searchstring or
replace(replace(number, '-', ''), ' ', '') like @searchstring
为此,我使用关键字标记器和带有catenate的delimeter过滤器创建了分析器,如下所示
"search_delimiter": {
"split_on_numerics": "false",
"generate_word_parts": "false",
"preserve_original": "false",
"generate_number_parts": "false",
"catenate_all": "true",
"split_on_case_change": "false",
"type": "word_delimiter",
"stem_english_possessive": "false"
}
"analyzer": {
"searchanalyzer": {
"filter": [
"lowercase"
,
"search_delimiter"
],
"type": "custom",
"tokenizer": "keyword"
},
"Name": {
"analyzer": "searchanalyzer",
"type": "string",
"fields": {
"raw": {
"analyzer": "searchanalyzer",
"type": "string"
}
}
},
"Number": {
"analyzer": "searchanalyzer",
"type": "string",
"fields": {
"raw": {
"analyzer": "searchanalyzer",
"type": "string"
}
}
}
"ShortDescription": {
"analyzer": "searchanalyzer",
"type": "string",
"fields": {
"raw": {
"analyzer": "searchanalyzer",
"type": "string"
}
}
},
结果是
curl -XGET "Index/_analyze?analyzer=searchanalyzer&pretty=true" -d "Original Brother TN-241C Toner Cyan"
{
"tokens" : [ {
"token" : "originalbrothertn241ctonercyan",
"start_offset" : 0,
"end_offset" : 35,
"type" : "word",
"position" : 0
} ]
}
}
因此,我需要基本上应用相同的分析器,并使用查询字符串或通配符搜索来进行instring搜索。
所以如果我像下面这样搜索
"query": {
"query_string" : {
"fields" : ["Name", "Number", "ShortDescription"],
"query" : "*TonerCyan*"
}
}
它很好用,但是如果我搜索
"query": {
"query_string" : {
"fields" : ["Name", "Number", "ShortDescription"],
"query" : "*Toner Cyan*"
}
}
它不会返回任何结果,这意味着SearchAnalyzer在执行查询字符串之前不会应用,因为我希望它在第二个查询中搜索toner cyan,而不是分别搜索toner和青色?第一个问题是为什么这行不通?第二,实现上述TSQL查询的最佳方法是什么?它应该搜索多个字段