Multi-index, multi-type field comparison query

[jclose (Jason)]

I'm trying to do a join-esque type of query, where I want to find results where fieldA == fieldB, where fieldA is of typeA and is in indexA, and fieldB is of typeB and is in indexB.

The consensus on how to perform this comparison is with a query script field. I can't really find an example to perform the rest of it.

Here is what I came up with, but I'm getting an error:

{
    "query" : {
        "bool" : {
            "filter": {
                "bool" : {
                    "should" : [
                        {"range": {"@timestamp" : { "gt": "now-12h" }}},
                        {"type": {"value":"typeA"}},
                        {"type": {"value":"typeB"}}
                    ]
                }
            },
            "must" : 
            {
                "script" : {
                    "script" : {
                        "inline" : "doc[fieldA].value == doc.[fieldB].value",
                        "lang": "painless"
                    }
                }
            }
        }
    }
}

I get an error with this. Is there something I am missing?

[polyfractal (Zachary Tong)]

What error are you getting?

[jclose (Jason)]

{"error":{"root_cause":[{"type":"script_exception","reason":"compile error","script_stack":["... c[fieldA].value == doc.[fieldB].value"," ^---- HERE"],"script":"doc[fieldA].value == doc.[fieldB].value","lang":"painless"}...{"type":"circuit_breaking_exception","reason":"[script] Too many dynamic script compilations within one minute, max: [15/min]; please use on-disk, indexed, or scripts with parameters instead; this limit can be changed by the [script.max_compilations_per_minute] setting","bytes_wanted":0,"bytes_limit":0}...

And it keeps repeating that over and over. I narrowed the time to just 1h and it is doing the same thing. We're not talking about a ton of data.

[polyfractal (Zachary Tong)]

Are you dynamically changing "fieldA" and "fieldB" client-side when generating the script?

Elasticsearch takes the script string (doc[fieldA].value == doc.[fieldB].value) compiles it and caches it for future use. If the next time you execute a query with different fields (doc[fieldA].value == doc.[fieldC].value), that has to be compiled and cached independently.

The error you're getting is basically saying you're trying to compile too many different scripts too quickly.

Instead, you should use parameters:

{
  "script" : {
    "script" : {
         "inline" : "doc[first_field].value == doc.[second_field].value",
         "lang": "painless",
         "params" : {
           "first_field":  "fieldA",
           "second_field": "fieldB"
         }
     }
  }
}

Now, only a single script is compiled and cached (doc[first_field].value == doc.[second_field].value) but since it has been parameterized, you can repeatedly change the field values without causing a recompilation.