How to combine "must" and "should" in Elasticsearch query?

Question

I need to "translate" this pseudo-SQL query in Elasticsearch query DSL:

select from invoice where invoiceType = 'REGULAR' and receiver = 'CUSTOMER' and (invoiceStatus = 'DISPATCHED' or invoiceStatus = 'PAYED')

I have this:

{
  "query": {
    "bool": {
      "must": [
        { "match": { "invoiceType":  "REGULAR" }},
        { "match": { "receiver": "CUSTOMER" }},
        { "bool" : {
            "should": [
                {"match" : {"invoiceStatus": "DISPATCHED"}},
                {"match" : {"invoiceStatus": "PAYED"}}
            ]
          }
        }
      ]
    }
  }
}

That query is returning 0 results, but I know there are many that matches what I'm searching for. AFAIK, must would be like 'AND' and should like 'OR'. What am I missing?

Answer 1

Not sure that it will work for you or not but you can make a try and see what you get? Though I did some change with match to term. Hope this will help you.

GET /invoice/_search
{
   "query" : {
      "constant_score" : {
         "filter" : {
            "bool" : {
              "must" : [
                { "term" : {"invoiceType" : "REGULAR"}}, 
                { "term": { "receiver": "CUSTOMER" }},
                { "bool" : { 
                  "should" : [
                      {"terms": {"invoiceStatus": ["DISPATCHED","PAYED"]}}   
                  ]
                }}
              ]
           }
         }
      }
   }
}

OR

GET /invoice/_search
{
   "query" : {
      "constant_score" : {
         "filter" : {
            "bool" : {
              "must" : [
                { "term" : {"invoiceType" : "REGULAR"}}, 
                { "term": { "receiver": "CUSTOMER" }},
                { "bool" : { 
                  "should" : [
                    {"term": {"invoiceStatus": "DISPATCHED"}},
                    {"term": {"invoiceStatus": "PAYED"}}     
                  ]
                }}
              ]
           }
         }
      }
   }
}