Converting SQL into Elasticsearch DSL for AND case

Question

I have an SQL query like this

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%');"

I am trying to convert it to Elasticsearch query. Here is the query I tried. It gives an OR result instead of 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"}}
                    ]
                }
            }
        }
    }
}

I have an even big query that I have to convert after my first query is correct.

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;"

How to convert my SQL query, so that I get an AND result instead of OR?

jaspreet chahal · Accepted Answer · 2020-04-23 10:55:18Z

For "OR" you can use "should" with minimum_should_match:1

For "AND" you can use "must"

Filter is used if you don't want to calculate score for search result. constant_score -returns every matching document with a relevance score equal to the boost parameter value.

In your case filter alone might be sufficient, if you want to use constant_score then wrap filter query with constant_score and use boost

Query1:

{
  "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
              }
            }
          ]
        }
      }
    }
  }
}

Query2:

{
  "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
                }
              }
            }
          ]
        }
      }
    }
  }
}

Collectives™ on Stack Overflow

Converting SQL into Elasticsearch DSL for AND case

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related