Wildcard query with second condition on other field

Elastic Stack Elasticsearch
1

I would execute a query like this (sql like):

SELECT * FROM MYINDEX WHERE SUGGEST LIKE %FOO% AND YEAR=1999

How can I do in Elastic Search?

I write a query with wild card but I don't know how to combine it with and clause that match year=1999:

{
    "query": {
        "wildcard" : { "name" : "*foo*" }
    }
}

Can you help me?

2

Use a bool query.

(And don't use wildcards but this is another story)

3

This is my index:
{
"mappings": {
"records" : {
"properties" : {
"suggest" : {
"type" : "completion",
"contexts": [
{
"name": "year",
"type": "category",
"path": "year"
}
]
}
}
}
}
}

4

How can be done with this mappings?

What can be done? The query part?

5

Yes, I would do the query with LIKE %FOO% AND YEAR=1999

6

So:

Something like:

POST _search
{
  "query": {
    "bool" : {
      "must" : {
        "wildcard" : { "name" : "%FOO%" }
      },
      "filter": {
        "term" : { "year" : 1999 }
      }
    }
  }
}

Some note about wildcard from doc: Wildcard Query | Elasticsearch Reference [6.2] | Elastic

Note that this query can be slow, as it needs to iterate over many terms. In order to prevent extremely slow wildcard queries, a wildcard term should not start with one of the wildcards * or ?.