Sum Aggregation using multiple fields

[searchwithme (search user)]

Hi team!

I am indexing the following sample data:

travel index

Flight price - 550
Hotel price - 220
Meals - 120

(Total Expense - 890)

1. If I query for travel index with total expense < 1000, it should return results as the total expense < 1000
2. If I query for travel index with total expense > 1000, it should not return results as the total expense < 1000

Is there a way to query to get the sum of all the fields and then apply the filter say in this case <1000?

I see nested aggregations but not sure if it can be done across multiple fields

[dadoonet (David Pilato)]

Welcome!

What does a typical json document looks like?

[searchwithme (search user)]

{“city”:”Chicago”,
“Traveldate”:”12dec2022”,
“Flightprice”: “350”,
“Hotelprice”: “200”,
“Meals”: “120”
}

[dadoonet (David Pilato)]

And why not doing this at index time?

{
  "city":"Chicago",
  "Traveldate":"12dec2022",
  "Flightprice": 350,
  "Hotelprice": 200,
  "Meals": 120,
  "Total": 670
}

[searchwithme (search user)]

So create a new field called total expense in the document? How do I do the summation during indexing?

[dadoonet (David Pilato)]

The best thing is to do that in your application. Should not be hard.
But you can also use an ingest pipeline if the first solution does not work for you.