How to perform math formula in Elasticsearch script score based on field value conditions?

user_5458090 0
Viewed 211

I would like to apply a simple math formula to all documents in Elasticsearch DB. The tricky thing is that each variables consists of multiply values which are at the same time person attributes.

{
        "_id" : "1",
        
          "A" : [
            1,
            2,
            3,
            4
          ],
          "B" : [
            5,
            6
          ]
}

Here we have UserId = 1 having

  1. attributes 1,2,3,4 from source A
  2. attributes 5,6 from source B

And now, I would like to calculate the following formula for all documents:

5 * 1 (if A has value 2) or 5 * 0 (if A has not value 2) + 4 * 1 (if A has value 3) or 4 * 0 * (if A has not value 3)

In SQL it would be very simple:

SELECT 5 * A_2 + 4 * A_3 FROM table.

As in SQL table each attribute would be a separate column but in Elasticsearch we keep all information about a person (from all tables/sources) in one document.

I think it is feasible using CASE statements but I would prefer to do this in more elegant way, especially as sometimes we need to use 200 or more attributes in one equation and I do not want to have > 200 CASE statements.

I was trying something like this:

GET /index/_search
{
      "query": {
        "function_score": {
          "script_score": {
            "script": {
              "params": {
                "a": 5,
                "b": 4
              },
              "source": "if (doc['A'].values == 2) { return params.a} else { return 0 } + 
                         if (doc['A'].values == 3) { return params.b} else { return 0 }"

            }
          }
        }
      }
  }

But, ES can't handle with checking if '2' is an element of array A.

elasticsearch formula
1 Answers

I was able to achieve that using below query:

    GET /index/_search
{
    "query": {
        "function_score": {
            "query": {
                "match_all": {}
            },
            "script_score": {
                "script": {
                    "params": {
                        "a": 5,
                        "b": 4
                    },
                    "source": """
              double sum = 0.0; 
              double val = 0;
              for (item in doc['A'
                    ]) {
                try {
                  val = params.get(Long.toString(item))
                        } 
                catch (NullPointerException e){
                  val = 0
                        } if (val != 0) {
                  sum += val
                        }
                    } 
              return sum
                """
                }
            }
        }
    }
}

It works but the performance is not perfect. I get a timeout error after 5 minutes for 250 MM documents. Do you know how to achieve the same thing while reducing elapsed time?

user_54580900
Related