elasticsearch - 在AND情况下将SQL转换为Elasticsearch DSL

Question

我有这样的SQL查询

sql_1 = "SELECT * FROM dd_s3data WHERE (yelp_address = '370 Barren Rd' OR yelp_businessname ILIKE '%Computer%') AND (yelp_state = 'CT' OR yelp_category ILIKE '%flooring%');"

我正在尝试将其转换为Elasticsearch查询。这是我尝试过的查询。它给出一个 OR结果而不是 AND

es_query1 = {
    "query": {
        "constant_score": {
            "filter": {
                "bool": {
                    "should": [
                        {"match_phrase": {"yelp_address": "370 Barren Rd"}},
                        {"match": {"yelp_businessname": "Computer"}}
                    ],
                    "should": [
                        {"match": {"yelp_state": "CT"}},
                        {"match_phrase": {"yelp_category": "flooring"}}
                    ]
                }
            }
        }
    }
}

我有一个很大的查询，我的第一个查询正确后必须转换。

sql_2 = "SELECT * FROM dd_s3data WHERE (yelp_address = '370 Barren Rd' OR yelp_businessname ILIKE '%Computer%') AND (yelp_state = 'CT' OR yelp_category ILIKE '%flooring%') AND yelp_noofreviews < 3.0 AND yelp_noofrating > 3.0;"

如何转换我的SQL查询，以便获得 AND结果而不是 OR？

Answer 1

对于“OR”，可以将“should”与minimum_should_match:1结合使用

对于“AND”，您可以使用“must”

如果您不想计算搜索结果的分数，则使用过滤器。
constant_score-返回每个匹配文档的相关性得分等于boost参数值。

在您的情况下，仅过滤器就足够了，如果您想使用constant_score，则将过滤器查询与constant_score合并并使用boost

查询1:

{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "bool": {
                "should": [
                  {
                    "match_phrase": {
                      "yelp_address": "370 Barren Rd"
                    }
                  },
                  {
                    "match": {
                      "yelp_businessname": "Computer"
                    }
                  }
                ],
                "minimum_should_match":1
              }
            },
            {
              "bool": {
                "should": [
                  {
                    "match": {
                      "yelp_state": "CT"
                    }
                  },
                  {
                    "match_phrase": {
                      "yelp_category": "flooring"
                    }
                  }
                ],
                "minimum_should_match":1
              }
            }
          ]
        }
      }
    }
  }
}

查询2:

{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "bool": {
                "should": [
                  {
                    "match_phrase": {
                      "yelp_address": "370 Barren Rd"
                    }
                  },
                  {
                    "match": {
                      "yelp_businessname": "Computer"
                    }
                  }
                ]

              }
            },
            {
              "bool": {
                "should": [
                  {
                    "match": {
                      "yelp_state": "CT"
                    }
                  },
                  {
                    "match_phrase": {
                      "yelp_category": "flooring"
                    }
                  }
                ],
                "minimum_should_match":1
              }
            },
            {
              "range": {
                "yelp_noofreviews": {
                  "lt": 3
                }
              }
            },
            {
              "range": {
                "yelp_noofrating": {
                  "gt": 3
                }
              }
            }
          ]
        }
      }
    }
  }
}