SQL喜欢与and Or的Elasticsearch中的查询

Question

嗨，我仍在与Elasticsearch(v6.1.3(一起玩，发现它很棒，但仍然struglling。>

from elasticsearch import Elasticsearch
es = Elasticsearch()
if not es.indices.exists(index="my-index"):
    customset={
        "settings": {
            "analysis": {
                "analyzer": {
                    "ngram_analyzer": {
                        "tokenizer": "ngram_tokenizer",
                        "filter": [
                            "lowercase",
                            "asciifolding"
                        ]
                    }
                },
                "tokenizer": {
                    "ngram_tokenizer": {
                        "type": "ngram",
                        "min_gram": 3,
                        "max_gram": 100,
                        "token_chars": [
                            "letter",
                            "digit"
                        ]
                    }
                }
            }
        },
        "mappings": {
            "my-type": {
                "properties": {
                    "demodata": {
                        "type": "text",
                        "fields": {
                            "ngram": {
                                "type": "text",
                                "analyzer": "ngram_analyzer",
                                "search_analyzer": "standard"
                            }
                        }
                    },
                    "Field1": {
                        "type": "text",
                        "fields": {
                            "ngram": {
                                "type": "text",
                                "analyzer": "ngram_analyzer",
                                "search_analyzer": "standard"
                            }
                        }
                    }
                }
            }
        }
    }
    es.indices.create(index="my-index", body=customset, ignore=400)
    docs=[
    { "demodata": "hELLO" ,"field1":"Alex","field2":"Red"},
    { "demodata": "hi" ,"field1":"Tom","field2":"Blue"},
    { "demodata": "bye" ,"field1":"Jack","field2":"Green"},
    { "demodata": "HeLlo WoRld!","field1":"Robert","field2":"Yellow" },
    { "demodata": "[e-mail]","field1":"Dave","field2":"White" }
    ]
    for doc in docs:
    res = es.index(index="my-index", doc_type="my-type", body=doc)

es.indices.refresh(index="my-index")
res=helpers.scan(client=es,scroll='2m',index="my-index", doc_type="my-type",query={"query": {"match": {"demodata.ngram": "Hello"}}})

现在我想按照sql的询问(假设我的SQL表名：my-type(：

SELECT * FROM my-type WHERE (demodata LIKE '%ell%' OR demodata LIKE '%orld%') AND (field1 LIKE '%red%' OR demodata LIKE '%yellow%')

这意味着我必须在不同字段中使用多术语进行搜索。谁能建议我怎么做？谢谢

Answer 1

{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "minimum_should_match": 1, 
            "should": [
              {
                "wildcard": {
                  "demodata": {
                    "value": "*ell*"
                  }
                }
              },
              {
                "wildcard": {
                  "demodata": {
                    "value": "*orld*"
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "minimum_should_match": 1, 
            "should": [
              {
                "wildcard": {
                  "demodata": {
                    "value": "*yellow*"
                  }
                }
              },
              {
                "wildcard": {
                  "field1": {
                    "value": "*red*"
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

上面是这样做的一种方法(我正在使用ES 5.3.3(。这将不会为您提供的数据集带来任何结果。尝试玩一点。还有其他实现相同行为的方法，但这是最直接的

Answer 2

我正在使用ES 5.6.2

{
"query": {
    "bool": {
        "should": {             
            "multi_match": {
                "query": "orld ell",
                "fields": [
                    "demodata"                                         
                ]
            }
        },
        "should": [
            "multi_match": {
                "query": "red",
                "fields": [
                    "field1"                       
                ]
            },
            "multi_match": {
                "query": "yellow",
                "fields": [
                    "demodata"                       
                ]
            }
        ]
    }
}

}