Error when converting sql into elastic dsl for sql AND case

[Digil]

I have a 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? Thanks...

[matriv (Marios Trivyzas)]

You can use the SQL translate API and use LIKE instead of ILIKE.