[FIXED] How do I get `sort` and `bool => should` query to work in tandem?

Issue

I have the following query for fetching all products. What I'm trying to achieve is keep the out of stock products I.E. products with stock_sum = 0 at the bottom:

{
  "sort": [
    {
      "updated_at": {
        "order": "desc"
      }
    }
  ],
  "size": 10,
  "from": 0,
  "query": {
    "bool": {
      "should": [
        {
          "range": {
            "stock_sum": {
              "gte": 1,
              "boost": 5
            }
          }
        }
      ]
    }
  }
}

But with the above query sort seems to completely override should, which is how it's suppose to behave I guess. A couple of things that I tried are changing the should to must in this case the out of stock products, are left out completely (that's not what I want, I still want the out of stock products at the bottom).

Another approach is remove sort, and then the should query seems to have an effect, but again I need the sort. So my question is how do I get sort and bool => should query to work in tandem ? I.E. sort by updated_at but also keep the stock_sum = 0 at the bottom?

Solution

Using match_all and constant_score query in the same should clause and sorting first by _score by asc, then by updated_at by desc should work for your example. Here is an example query:

{
  "sort": [
    {
      "_score": {
        "order": "asc"
      }
    },
    {
      "updated_at": {
        "order": "desc"
      }
    }
  ]
  "query": {
    "bool": {
      "should": [
        {
          "match_all": {}
        },
        {
          "constant_score": {
             "filter": {
              "term": {
                "stock_sum": 0
              }
            },
            "boost": 10
          }
        }
      ]
    }
  }
}

Answered By - YD9

Answer Checked By - David Marino (PHPFixing Volunteer)