# Complex order on numerics and filtering

**URL:** <https://discuss.elastic.co/t/complex-order-on-numerics-and-filtering/371143>\
**Category:** Elasticsearch\
**Tags:** painless\
**Created:** [November 27, 2024, 2:36pm UTC](https://discuss.elastic.co/t/complex-order-on-numerics-and-filtering/371143 "2024-11-27T14:36:21Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![argy](https://avatars.discourse-cdn.com/v4/letter/a/f14d63/32.png) [@argy](https://discuss.elastic.co/u/argy)\
**Post date:** [November 27, 2024, 2:36pm UTC](https://discuss.elastic.co/t/complex-order-on-numerics-and-filtering/371143/1 "2024-11-27T14:36:22Z")

</div>

Good day,

I am developing a solution whereby I will have an index with below documents

```auto
{
          "name" : "Powlowski, Schaden and Kuvalis",
          "financials" : [
            {
              "revenue_value_type" : "range",
              "revenue_usd_average" : 11,
              "revenue_usd" : null,
              "fin_year" : 2023
            },
            {
              "revenue_value_type" : "exact",
              "revenue_usd_average" : null,
              "revenue_usd" : 100,
              "fin_year" : 2024
            },
            {
              "revenue_value_type" : "exact",
              "revenue_usd_average" : null,
              "revenue_usd" : 89,
              "fin_year" : 2022
            }
          ],
         "industries": ["Goods", "Healthcare"],
         "markets": ["IT", "Technology"]
        }

```

I want to be able to both sort on numerics for example revenue and filter on specific industries to get back the list of documents.

For example to order on the revenue of only the latest financial year I use something like below script

```auto
{
  "size": 50,
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "source": """
            def latestYear = 0;
            def latestFinancial = null;

            // Find the financial object with the latest year
            for (def financial : params['_source']['financials']) {
              if (financial['fin_year'] > latestYear) {
                latestYear = financial['fin_year'];
                latestFinancial = financial;
              }
            }

            if (latestFinancial == null) {
              return 0; // Default score if no financials exist
            }

            // Calculate the score for the latest financial object
            double revenue = 0;
            if (latestFinancial['revenue_value_type'] == 'range') {
              revenue = latestFinancial['revenue_usd_average'];
            } else if (latestFinancial['revenue_value_type'] == 'exact') {
              revenue = latestFinancial['revenue_usd'];
            }

            // Return only the revenue score
            return revenue;
          """
        },
        "order": "desc"
      }
    }
  ]
}

```

I wanted to ask if I'm on the right track or are much better ways to accomplish this, such as pre-computed aggregations etc.

Many thanks

---

<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:** [December 25, 2024, 2:37pm UTC](https://discuss.elastic.co/t/complex-order-on-numerics-and-filtering/371143/2 "2024-12-25T14:37:03Z")

</div>

Closed on December 25, 2024, 2:37pm UTC

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