# Greater than based on value from another field

**URL:** <https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089>\
**Category:** Elasticsearch\
**Created:** [April 23, 2018, 11:40am UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089 "2018-04-23T11:40:34Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dave\_Clissold](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dave_clissold/32/30296_2.png) [@Dave\_Clissold](https://discuss.elastic.co/u/Dave_Clissold)\
**Post date:** [April 23, 2018, 11:40am UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/1 "2018-04-23T11:40:34Z")

</div>

Hi, I am trying to build a query that will match when an value of a field is greater than another nested field, but am stuck as to where to begin. Can anyone help or suggest somewhere to begin reading. Example data:

```
 {
  "id":"1",
  "contains":[
      {
        "amount":20,
        "name":"com1"
      },
      {
        "amount":40,
        "name":"com2"
      }
  ]
},
{
  "id":"2",
  "contains":[
      {
        "amount":40,
        "name":"com1"
      },
      {
        "amount":30,
        "name":"com2"
      }
  ]
}

```

So in the above, my query would be where the amount of com1 is great than the amount of com2, which would return "2"

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [April 23, 2018, 12:11pm UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/2 "2018-04-23T12:11:30Z")

</div>

If it were possible it would probably take the form of some custom script retrieving the value pairs and comparing them for every doc. This would be linear to the number of docs and sounds expensive so perhaps it's worth considering another approach. Perhaps you could compute this "on the way in" using an ingest-pipeline to compute the difference between the amounts. You could then query and aggregate on this numeric "diff" value held in the index much more efficiently.

---

<div class="post-metadata">

**Author:** ![Dave\_Clissold](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dave_clissold/32/30296_2.png) [@Dave\_Clissold](https://discuss.elastic.co/u/Dave_Clissold)\
**Post date:** [April 23, 2018, 12:36pm UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/3 "2018-04-23T12:36:36Z")

</div>

That is an idea, simple enough to build a simple string or array with an order and then just additionally match that at query time.

Do you think something similar to this would be efficient?

> [@Matching by array elements](https://discuss.elastic.co/t/matching-by-array-elements/39530/2):
>
> There isn't an efficient way to do this but I can think of two OK ways to do it and I'm not sure which one is best: Index the documents with the string array just like that and search using a bool query (bool filter in 1.x) with each term in the OR clause, probably wrapped in a constant\_score query. Then you use a script to recheck all the found documents for your restriction. Index the documents with an extra field that smooshes together the string array. So it'd be {"stringArray": ["A",…

Where I use the principal of the 'smooshed\_arrays' to create com1\_com2. the reality of my searches are likely to be 2 or 3 objects to work with, so can easily build these on the way in for example, A\_B and A\_B\_C / A\_C\_B (when 3 items to query)

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [April 23, 2018, 1:00pm UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/4 "2018-04-23T13:00:21Z")

</div>

> Where I use the principal of the 'smooshed\_arrays' to create com1\_com2. the reality of my searches are

I'm not sure what you intend these arrays to represent in this example.  
If they are your `name` fields, sorted by their `amount` then, yes that or a concatenated `com1_com2` type string might help you find records more easily. I'm not clear on these points:

- how many `com*` type names can exist in each doc?
- are you only interested in com1 and com2 or testing arbitrary choices of com\* names?
- do you only care about pairs or maybe sequences of more than 2 names when sorted by amount?

Maybe sharing the actual business problem you're trying to solve would help?

---

<div class="post-metadata">

**Author:** ![Dave\_Clissold](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dave_clissold/32/30296_2.png) [@Dave\_Clissold](https://discuss.elastic.co/u/Dave_Clissold)\
**Post date:** [April 23, 2018, 1:39pm UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/5 "2018-04-23T13:39:19Z")

</div>

Sorry yes, the com\* are names and the amounts are not necessary for this section of the query, it is purely com1 value is greater than com2 so strings are fine. To answer your questions

1- There can be 5-6 com\*, but when above 3 this ordering of amounts becomes a useless query and the results are not really worth it.

2- yes only interested in the names. the amounts are not really of value, it's just com1 is greater than com2.

1. yes it is only the sequence. Just had a thought that I can add a numerical value for the index position in the sequence and then query that, unless there is a way of assigning this on ingest?

The problem is amount of ingredients in cocktails, so the user can say they want a cocktail with more gin than vodka

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [April 23, 2018, 2:01pm UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/6 "2018-04-23T14:01:34Z")

</div>

> [@Dave\_Clissold](#):
>
> The problem is amount of ingredients in cocktails, so the user can say they want a cocktail with more gin than vodka

The applications of elasticsearch never cease to amaze me 😉

You might want to take a look at [SpanQuery](https://www.elastic.co/guide/en/elasticsearch/reference/6.2/span-queries.html) family of queries - specifically the SpanNear query which can help you test the position and order of elements.

---

<div class="post-metadata">

**Author:** ![Dave\_Clissold](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dave_clissold/32/30296_2.png) [@Dave\_Clissold](https://discuss.elastic.co/u/Dave_Clissold)\
**Post date:** [April 23, 2018, 2:05pm UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/7 "2018-04-23T14:05:49Z")

</div>

> [@Mark\_Harwood](#):
>
> The applications of elasticsearch never cease to amaze me 😉

Crazy right? Come up with something amazing like ES and we'll use it for almost everything we can possibly conceive, including something as trivial as wanting more gin!!!!!

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [April 23, 2018, 2:32pm UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/8 "2018-04-23T14:32:02Z")

</div>

In terms of mappings required to support span queries - you'll obviously need to ensure ingredients are sorted by amount. As for how you pass these values

- a single JSON string value would require a delimiter in there e.g. commas and a choice of Analyzer that chops into tokens based on the delimiter OR
- a JSON array of the ingredients where you might want to dial back the [position\_increment](https://www.elastic.co/guide/en/elasticsearch/reference/6.x/position-increment-gap.html) to 1 to make the ingredient terms appear closer to each other and so wouldn't need big "slop" values in your SpanNear query

---

<div class="post-metadata">

**Author:** ![Dave\_Clissold](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dave_clissold/32/30296_2.png) [@Dave\_Clissold](https://discuss.elastic.co/u/Dave_Clissold)\
**Post date:** [April 23, 2018, 10:17pm UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/9 "2018-04-23T22:17:20Z")

</div>

I think I'll use the first option, easier method for getting our data in that format, than trying to implement a position increment.

Thanks for your help Mark. Much appreciated

---

<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:** [May 21, 2018, 10:21pm UTC](https://discuss.elastic.co/t/greater-than-based-on-value-from-another-field/129089/10 "2018-05-21T22:21:18Z")

</div>

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