How to boost results on the basis of fields values

Question

I have stuck into creating query for this problem. the query will a keyword search along with scoring of the products. I have these list of products

[
  {
    name: 'pro',
    clickCount: 110,
    bookingCount: 57,
    isPromoted: 0,
  },
  {
    name: 'prod',
    clickCount: 13,
    bookingCount: 77,
    isPromoted: 0,
  },
  {
    name: 'produ',
    clickCount: 43,
    bookingCount: 10,
    isPromoted: 0,
  },
  {
    name: 'produc',
    clickCount: 5,
    bookingCount: 17,
    isPromoted: 0,
  },
  {
    name: 'product',
    clickCount: 89,
    bookingCount: 67,
    isPromoted: 0,
  },
  {
    name: 'products',
    clickCount: 1,
    bookingCount: 2,
    isPromoted: 1,
  },
  {
    name: 'products2',
    clickCount: 3,
    bookingCount: 4,
    isPromoted: 1,
  },
]

I need to sort these into the below order:

1. Products having isPromoted=1 will be coming at the top (if there are multiple products with same values then the one with MAX(clickCount+bookingCount) will take precedence.
2. Products with MAX(clickCount+bookingCount) will come after that.

The final ordering for a keyword search pro should be this:

[
  {
    name: 'products2',
    clickCount: 3,
    bookingCount: 4,
    isPromoted: 1,
  },
  {
    name: 'products',
    clickCount: 1,
    bookingCount: 2,
    isPromoted: 1,
  },
  {
    name: 'pro',
    clickCount: 110,
    bookingCount: 57,
    isPromoted: 0,
  },
  {
    name: 'product',
    clickCount: 89,
    bookingCount: 67,
    isPromoted: 0,
  },
  {
    name: 'prod',
    clickCount: 13,
    bookingCount: 77,
    isPromoted: 0,
  },
  {
    name: 'produ',
    clickCount: 43,
    bookingCount: 10,
    isPromoted: 0,
  },
  {
    name: 'produc',
    clickCount: 5,
    bookingCount: 17,
    isPromoted: 0,
  },
]

I am writing this query but its not giving the desired result.

GET /products/_search
{
  
  "query": {
    "function_score": {
      "query": {
        "wildcard": {
          "name": "*pro*"
        }
      },
      
      "functions": [
         {
          "field_value_factor": {
            "field": "clickCount",
            "factor": 1.2,
            "modifier": "sqrt"
          }
         },
         {
          "field_value_factor": {
            "field": "clickCount",
            "factor": 1,
            "modifier": "sqrt"
          }
         },
         {
          "field_value_factor": {
            "field": "isPromoted",
            "factor": 1.5,
            "modifier": "sqrt"
          },
          "weight": 20
         }
        
      ],
      "boost_mode": "sum"
    }
  }
}

Answer 1

For what you want, I think you can compute a totalCount field at runtime which returns the sum of the counts. Then you can simply just need to sort using the isPromoted field first, then the totalCount field:

Mappings:

{
    "mappings": {
        "properties": {
            "name": {
                "type": "text"
            },
            "clickCount": {
                "type": "integer"
            },
            "bookingCount": {
                "type": "integer"
            },
            "isPromoted": {
                "type": "integer"
            }
        }
    }
}

And the search query:

{
  "runtime_mappings": {
    "totalCount": {
      "type": "long",
      "script": "emit(doc['clickCount'].value + doc['bookingCount'].value);"
    }
  },
  "query": {
        "match_all": {}
  },
    "sort": [
        {"isPromoted": "desc"},
        {"totalCount": "desc"}
    ]
}