# Multi-index, multi-type field comparison query

**URL:** <https://discuss.elastic.co/t/multi-index-multi-type-field-comparison-query/95579>\
**Category:** Elasticsearch\
**Created:** [August 2, 2017, 4:40pm UTC](https://discuss.elastic.co/t/multi-index-multi-type-field-comparison-query/95579 "2017-08-02T16:40:46Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jclose](https://avatars.discourse-cdn.com/v4/letter/j/df705f/32.png) [@jclose](https://discuss.elastic.co/u/jclose)\
**Post date:** [August 2, 2017, 4:40pm UTC](https://discuss.elastic.co/t/multi-index-multi-type-field-comparison-query/95579/1 "2017-08-02T16:40:46Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [August 2, 2017, 7:35pm UTC](https://discuss.elastic.co/t/multi-index-multi-type-field-comparison-query/95579/2 "2017-08-02T19:35:20Z")

</div>

What error are you getting?

---

<div class="post-metadata">

**Author:** ![jclose](https://avatars.discourse-cdn.com/v4/letter/j/df705f/32.png) [@jclose](https://discuss.elastic.co/u/jclose)\
**Post date:** [August 2, 2017, 7:53pm UTC](https://discuss.elastic.co/t/multi-index-multi-type-field-comparison-query/95579/3 "2017-08-02T19:53:01Z")

</div>

> {"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.

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [August 2, 2017, 8:44pm UTC](https://discuss.elastic.co/t/multi-index-multi-type-field-comparison-query/95579/4 "2017-08-02T20:44:45Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting-using.html#prefer-params):

```auto
{
  "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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 30, 2017, 8:44pm UTC](https://discuss.elastic.co/t/multi-index-multi-type-field-comparison-query/95579/5 "2017-08-30T20:44:49Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.