Creating a SQL LIKE equivalent query with elasticsearch

26/08/2017 - ELASTICSEARCH

In this example we're going to some elasticsearch query examples which behave like classic SQL LIKE clause. For more information read Partial Matching.


Note


Wildcard option cannot be used in filtered queries.


Mapping


"name": {
  "type": "string",
  "index": "not_analyzed" # Not a full-text search
}

Single LIKE


SELECT * FROM products WHERE name LIKE 'Product1*'


prefix - query


{
  "query": {
    "prefix": {
      "name.raw": "Product1"
    }
  }
}

wildcard - query


{
  "query": {
    "wildcard": {
      "name.raw": "Product1*"
    }
  }
}

regexp - query


{
  "query": {
    "regexp": {
      "name.raw": "Product1.*"
    }
  }
}

prefix - bool


{
  "query": {
    "bool": {
      "must": {
        "prefix": {
          "name.raw": "Product1"
        }
      }
    }
  }
}

{
  "query": {
    "bool": {
      "must": [
        {
          "prefix": {
            "name.raw": "Product1"
          }
        }
      ]
    }
  }
}

wildcard - bool


{
  "query": {
    "bool": {
      "must": {
        "wildcard": {
          "name.raw": "Product1*"
        }
      }
    }
  }
}

{
  "query": {
    "bool": {
      "must": [
        {
          "wildcard": {
            "name.raw": "Product1*"
          }
        }
      ]
    }
  }
}

regexp - bool


{
  "query": {
    "bool": {
      "must": {
        "regexp": {
          "name.raw": "Product1.*"
        }
      }
    }
  }
}

{
  "query": {
    "bool": {
      "must": [
        {
          "regexp": {
            "name.raw": "Product1.*"
          }
        }
      ]
    }
  }
}

prefix - filtered


{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": {
            "prefix": {
              "name.raw": "Product1"
            }
          }
        }
      }
    }
  }
}

{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "prefix": {
                "name.raw": "Product1"
              }
            }
          ]
        }
      }
    }
  }
}

regexp - filtered


{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": {
            "regexp": {
              "name.raw": "Product1.*"
            }
          }
        }
      }
    }
  }
}

{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "regexp": {
                "name.raw": "Product1.*"
              }
            }
          ]
        }
      }
    }
  }
}

Multiple LIKE


SELECT * FROM products WHERE name LIKE 'Product1*' AND colour LIKE 'P*'


prefix - bool


{
  "query": {
    "bool": {
      "must": [
        {
          "prefix": {
            "name": "Product1"
          }
        },
        {
          "prefix": {
            "colour": "P"
          }
        }
      ]
    }
  }
}

wildcard - bool


{
  "query": {
    "bool": {
      "must": [
        {
          "wildcard": {
            "name": "Product1*"
          }
        },
        {
          "wildcard": {
            "colour": "P*"
          }
        }
      ]
    }
  }
}

regexp - bool


{
  "query": {
    "bool": {
      "must": [
        {
          "regexp": {
            "name": "Product1.*"
          }
        },
        {
          "regexp": {
            "colour": "Shop7.*"
          }
        }
      ]
    }
  }
}

prefix - filtered


{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "prefix": {
                "name": "Product1"
              }
            },
            {
              "prefix": {
                "colour": "P"
              }
            }
          ]
        }
      }
    }
  }
}

regexp - filtered


{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "regexp": {
                "name": "Product1.*"
              }
            },
            {
              "regexp": {
                "colour": "P.*"
              }
            }
          ]
        }
      }
    }
  }
}

Multiple LIKE and additional search term


SELECT * FROM products WHERE name LIKE 'Product1*' AND colour LIKE 'P*' AND year = 2000


prefix - bool


{
  "query": {
    "bool": {
      "must": [
        {
          "prefix": {
            "name": "Product1"
          }
        },
        {
          "prefix": {
            "colour": "P"
          }
        },
        {
          "term": {
            "year": 2000
          }
        }
      ]
    }
  }
}

wildcard - bool


{
  "query": {
    "bool": {
      "must": [
        {
          "wildcard": {
            "name": "Product1*"
          }
        },
        {
          "wildcard": {
            "colour": "P*"
          }
        },
        {
          "term": {
            "year": 2000
          }
        }
      ]
    }
  }
}

regexp - bool


{
  "query": {
    "bool": {
      "must": [
        {
          "regexp": {
            "name": "Product1.*"
          }
        },
        {
          "regexp": {
            "colour": "Shop7.*"
          }
        },
        {
          "term": {
            "year": 2000
          }
        }
      ]
    }
  }
}

prefix - filtered


{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "prefix": {
                "name": "Product1"
              }
            },
            {
              "prefix": {
                "shops.name": "Shop7"
              }
            },
            {
              "term": {
                "year": 2000
              }
            }
          ]
        }
      }
    }
  }
}

regexp - filtered


{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "regexp": {
                "name": "Product1.*"
              }
            },
            {
              "regexp": {
                "colour": "P.*"
              }
            },
            {
              "term": {
                "year": 2000
              }
            }
          ]
        }
      }
    }
  }
}