# Modify sorting with painless script

**URL:** <https://discuss.elastic.co/t/modify-sorting-with-painless-script/146993>\
**Category:** Elasticsearch\
**Created:** [September 2, 2018, 7:21pm UTC](https://discuss.elastic.co/t/modify-sorting-with-painless-script/146993 "2018-09-02T19:21:03Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![aimeos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aimeos/32/35066_2.png) [@aimeos](https://discuss.elastic.co/u/aimeos)\
**Post date:** [September 2, 2018, 7:21pm UTC](https://discuss.elastic.co/t/modify-sorting-with-painless-script/146993/1 "2018-09-02T19:21:03Z")

</div>

We would like to store products including their categories and category positions in a document. One product can be in several categories at different positions. Our thought was to use something like this:

```
index.catalog.id = ['cat1', 'cat2', 'cat3']
index.catalog.position = ['00005|cat1', '00014|cat2', '00072|cat3']

```

For index.catalog.id we can filter and for index.catalog.position we can use for sorting. But if we use

```
['term' => ['index.catalog.id' => 'cat2']]
['sort' => 'index.catalog.positon']

```

the product would be at the fifth position instead of the fourteenth. Therefore, we would need a way to skip the index.catalog.position values for sorting, that are not relevant because they are for different categories.

Question: How can we do that with a painless script or is there a better way to achive the same in a different way?

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [September 4, 2018, 2:56pm UTC](https://discuss.elastic.co/t/modify-sorting-with-painless-script/146993/2 "2018-09-04T14:56:49Z")

</div>

The answer is: yes, you can do it with a Painless script. Something like this should work:

```auto
GET my_index/_search
{
  "query": {
    "term": {
      "index.catalog.id": "cat2"
    }
  },
  "sort": {
    "_script": {
      "type": "string",
      "script": {
        "lang": "painless",
        "source": "for (value in doc['index.catalog.position.keyword'].values) { StringTokenizer str = new StringTokenizer(value, '|'); def returnValue = str.nextElement(); if (params.query_term.equals(str.nextElement())) { return returnValue } } return 0",
        "params": {
          "query_term": "cat2"
        }
      },
      "order": "asc"
    }
  }
}

```

(Note that it requires you to also pass in your search terms as a parameter to the script)

Now, the question is: **should** you do it with a script? I would say: if you can model your data in such a way that you do not need scripting, then that's probably a better solution. Especially on larger dataset, running a lot of scripts can put a strain on your cluster.

For example, maybe you could model your data using [nested types](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html):

```auto
PUT my_index
{
  "mappings": {
    "_doc": {
      "properties": {
        "index.catalog": {
          "type": "nested",
          "properties": {
            "id": {
              "type": "keyword"
            },
            "position": {
              "type": "keyword"
            }
          }
        }
      }
    }
  }
}

```

Now, you could index your documents like this:

```auto
PUT my_index/_doc/1
{
  "index.catalog": [
    {
      "id": "cat1",
      "position": "0005|cat"
    },
    {
      "id": "cat2",
      "position": "0014|cat2"
    },
    {
      "id": "cat3",
      "position": "0072|cat3"
    }
  ]
}

```

Or even:

```auto
PUT my_index/_doc/1
{
  "index.catalog": [
    {
      "id": "cat1",
      "position": "0005"
    },
    {
      "id": "cat2",
      "position": "0014"
    },
    {
      "id": "cat3",
      "position": "0072"
    }
  ]
}

```

Now, you will be able to query and [sort like this, without the need for scripting](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-sort.html#nested-sorting):

```auto
GET my_index/_search
{
  "query": {
    "nested": {
      "path": "index.catalog",
      "query": {
        "term": {
          "index.catalog.id": "cat2"
        }
      }
    }
  },
  "sort": [
    {
      "index.catalog.position": {
        "order": "asc",
        "nested": {
          "path": "index.catalog",
          "filter": {
            "term": {
              "index.catalog.id": "cat2"
            }
          }
        }
      }
    }
  ]
}

```

---

<div class="post-metadata">

**Author:** ![aimeos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aimeos/32/35066_2.png) [@aimeos](https://discuss.elastic.co/u/aimeos)\
**Post date:** [September 4, 2018, 7:56pm UTC](https://discuss.elastic.co/t/modify-sorting-with-painless-script/146993/3 "2018-09-04T19:56:44Z")

</div>

Thank you for your detailed explanations! 🙂  
So essence is that the penality of nested types is less than the time needed to run the script to filter out all non-matching sorting keywords, so we will use nested types instead as you recommend

---

<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:** [October 2, 2018, 7:56pm UTC](https://discuss.elastic.co/t/modify-sorting-with-painless-script/146993/4 "2018-10-02T19:56:56Z")

</div>

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