Multiple Wildcard conditions on same field in Elasticsearch Kibana

Question

I am looking for something equivalent to this in elasticsearch -

Select * from users where firstname in ("%abc%" , "%xyz%" ) and city = "Texas"

It has multi match conditions and one fixed condition as CITY.

 "query": {
        "bool": {
          "must": [
            {
              "wildcard": {
                "firstname": "*ABC*"
              }
            },
            {
              "wildcard": {
                "firstname": "*XYZ*"
              }
            },
            {
              "wildcard": {
                "city": "Texas"
              }
            }
          ]
        }
      }

I am trying like above but it does not work. I am able to make it work with only one wildcard parameter. But as soon i am trying to keep multiple for same field i get zero results.

Answer 1

must is an AND condition.

{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "wildcard": {
                  "firstname": "*ABC*"
                }
              },
              {
                "wildcard": {
                  "firstname": "*XYZ*"
                }
              }
            ]
          }
        },
        {
          "wildcard": {
            "city": "Texas"
          }
        }
      ]
    }
  }
}

So SQL in clauses are translated as OR i.e should