Convert MySQL query (with where condition) to Elasticsearch query

Question

I need to "transform" mysql query to Elasticsearch query. What is blocking me is "where" statement. Basically what I need is to find all items that are within distance of 25 miles based on latitude and longitude and where pickup is enabled OR where items do have delivery enabled and provided zip code

where
(
    (
        `enabled_pickup` = '1'
        and(
            ST_Distance_Sphere(
                POINT(- 122.41941550000001 , 37.7749295) ,
                geolocation
            ) / 1000 * 0.62137119223733
        ) <= 25
    )
    or(
        `enabled_delivery` = '1'
        `zip_code` = '94116'
    )
)

and this is Elasticsearch query that is not working as expected

{
"query": {
    "bool": {
      "must": [
        {
      "bool": {
        "should": [
          {
            "bool": {
              "must": [
                {
                  "match": {
                    "enabled_pickup": "1"
                  }
                },
                {
                  "geo_distance": {
                    "distance": "25 mi",
                    "geo_location": {
                      "lat": "37.7749295",
                      "lon": "-122.41941550000001"
                    }
                  }
                }
              ]
            }
          },
          {
            "bool": {
              "must": [
                {
                  "term": {
                    "dispensary.delivery": "1"
                  }
                },
                {
                  "term": {
                    "zip_code": "94116"
                  }
                }
              ]
            }
          }
        ]
      }
    }
  ]
}

} }

can somebody please point me in right direction?

score 0 · Accepted Answer · answered Nov 14 '17 at 11:50

I was reading about the similar issue, you can give a try with below query. you can find a similar question being solved by this stackoverflow discussion

Hope this helps, Cheers!

{
"query": {
        "bool": {
            "should": [
                {
                    "bool": {
                      "must": [
                            {"match": { "enabled_pickup": "1"}}
                            ,{ "match": {
                                  "geo_distance": { 
                                        "distance": "25 mi", 
                                        "geo_location": {
                                            "lat": "37.7749295",
                                            "lon": "-122.41941550000001"
                                        }
                                    }
                                }
                            }
                        ]
                    }
                  ,
                    "bool": {
                      "must": [
                            { "match": {"dispensary.delivery": "1"} }
                            ,{"match": {"zip_code": "94116"} }
                        ]
                    }
                }
            ]
        }
    }
}

Convert MySQL query (with where condition) to Elasticsearch query

1 Answers1