Elasticsearch multi-field and conditional sorting: Painless script implementation guide

This article deeply explores the implementation method of multi-field sorting based on complex business logic in Elasticsearch. Through the Painless script, we show how to flexibly adjust the sort order of the `createdAt` field according to the presence or absence of the `tags` field in the document, that is, those with tags are sorted in ascending order of `createdAt`, and those without tags are sorted in descending order of `createdAt`, thereby meeting advanced customized sorting requirements.

In Elasticsearch, conventional field sorting is usually to directly arrange one or more fields in ascending order (asc) or descending order (desc). However, when business requirements involve more complex conditional logic, such as determining the sorting direction of a field based on the presence or absence of another field, the standard sorting mechanism cannot directly satisfy it. At this time, Elasticsearch's script-based sorting function is particularly powerful and flexible.

scene description

Suppose we have the following document structure, including the createdAt date field and tags keyword array field:

 doc1:
{
    "createdAt": "2022-11-25T09:45:00.000Z",
    "tags": [
      "Response Needed"
    ]
}
doc2:
{
    "createdAt": "2022-11-24T09:45:00.000Z",
    "tags": [
      "Customer care","Response Needed"
    ]
}
doc3:
{
    "createdAt": "2022-11-24T09:45:00.000Z",
    "tags": [

    ]
}

Our sorting requirements are:

1. First, sort according to the presence or absence of the tags field: documents with tags are ranked first, documents without tags are ranked last.
2. Secondly, for documents with tags, sort them in ascending order of the createdAt field.
3. Finally, for documents without tags, sort them in descending order of the createdAt field.

Solution: Painless script sorting

In order to implement the above complex conditional sorting, we will use Painless script to dynamically calculate the sorting value during the sorting stage.

1. Index mapping and data preparation

First, we need to create an index and define the field mapping, especially createdAt as date type and tags as keyword type so that the script can access and process it correctly.

 PUT idx_conditional_sort
{
  "mappings": {
    "properties": {
      "createdAt": {
        "type": "date"
      },
      "tags": {
        "type": "keyword"
      }
    }
  }
}

Next, we insert some sample documents for testing:

 POST idx_conditional_sort/_doc
{
    "createdAt": "2022-11-25T09:45:00.000Z",
    "tags": [
      "Response Needed"
    ]
}

POST idx_conditional_sort/_doc
{
    "createdAt": "2022-11-24T09:45:00.000Z",
    "tags": [
      "Response 02"
    ]
}

POST idx_conditional_sort/_doc
{
    "createdAt": "2022-11-24T09:45:00.000Z",
    "tags": [
      "Customer care","Response Needed"
    ]
}

POST idx_conditional_sort/_doc
{
    "createdAt": "2022-11-26T09:45:00.000Z",
    "tags": []
}

POST idx_conditional_sort/_doc
{
    "createdAt": "2022-11-23T09:45:00.000Z",
    "tags": []
}

2. Implement script sorting

We will use two scripts to sort elements in an array to implement multi-stage and conditional sorting.

Sorting logic decomposition:

• First-level sorting (script 1): used to distinguish documents with tags and documents without tags.

  • If the tags array is not empty, the script returns a higher value (eg 1).
  • If the tags array is empty, the script returns a lower value (eg 0).
  • Sort the results of this script in descending order (desc), ensuring that documents with tags take precedence.

• Second-level sorting (script 2): used to conditionally sort createdAt based on tags status.

  • If the tags array is not empty: directly return the millisecond timestamp of createdAt, so that the ascending order of createdAt will be achieved when the overall ascending order is arranged.
  • If the tags array is empty: return the negative value of createdAt millisecond timestamp, so that when the overall ascending order is arranged, the descending order of createdAt will be achieved (because the larger the negative value, the earlier the actual time).

Complete query statement:

 GET idx_conditional_sort/_search
{
  "sort": [
    {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "source": """
            // Script 1: Group according to whether tags exist // Return 1 for tags, return 0 for no tags return doc['tags.keyword'].size() > 0 ? 1 : 0;
          """
        },
        "order": "desc" // Sort in descending order, ensuring that tagged documents are at the front}
    },
    {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "source": """
            // Script 2: Conditionally sort createdAt based on tags status long createdAtMillis = doc['createdAt'].value.toInstant().toEpochMilli();
            if (doc['tags.keyword'].size() > 0) {
              // If there are tags, sort them in ascending order by createdAt and return the timestamp directly return createdAtMillis;
            } else {
              // If there is no label, sort by createdAt in descending order and return the negative value of the timestamp // In this way, when sorting in overall ascending order, the greater the negative value (the smaller the absolute value), the later the actual time is, achieving the descending order effect return -createdAtMillis;
            }
          """
        },
        "order": "asc" // Arrange in ascending order, and use the internal logic of the script to implement conditional sorting}
    }
  ]
}

3. Interpretation of results

After executing the above query, Elasticsearch will return the following results (partially):

 {
  "took": ...,
  "timed_out": false,
  "_shards": ...,
  "hits": {
    "total": { "value": 5, "relation": "eq" },
    "max_score": null,
    "hits": [
      {
        "_index": "idx_conditional_sort",
        "_id": "t42r-oQBEoAIompjS0Xh",
        "_score": null,
        "_source": {
          "createdAt": "2022-11-24T09:45:00.000Z",
          "tags": [ "Response 02" ]
        },
        "sort": [ 1, 1669283100000 ] // Tag exists, createdAt ascending order},
      {
        "_index": "idx_conditional_sort",
        "_id": "uI2r-oQBEoAIompjS0Xj",
        "_score": null,
        "_source": {
          "createdAt": "2022-11-24T09:45:00.000Z",
          "tags": [ "Customer care", "Response Needed" ]
        },
        "sort": [ 1, 1669283100000 ] // Tag exists, createdAt ascending order},
      {
        "_index": "idx_conditional_sort",
        "_id": "sY2r-oQBEoAIompjS0Xg",
        "_score": null,
        "_source": {
          "createdAt": "2022-11-25T09:45:00.000Z",
          "tags": [ "Response Needed" ]
        },
        "sort": [ 1, 1669369500000 ] // Tag exists, createdAt ascending order},
      {
        "_index": "idx_conditional_sort",
        "_id": "uY2r-oQBEoAIompjS0Xk",
        "_score": null,
        "_source": {
          "createdAt": "2022-11-26T09:45:00.000Z",
          "tags": []
        },
        "sort": [ 0, -1669455900000 ] // Tag does not exist, createdAt descending order (negative value ascending order)
      },
      {
        "_index": "idx_conditional_sort",
        "_id": "uo2r-oQBEoAIompjS0Xl",
        "_score": null,
        "_source": {
          "createdAt": "2022-11-23T09:45:00.000Z",
          "tags": []
        },
        "sort": [ 0, -1669196700000 ] // Tag does not exist, createdAt descending order (negative value ascending order)
      }
    ]
  }
}

As you can see from the hits array, documents are first grouped according to the presence of the tags field (the first element of the sort array is 1 for tags and 0 for no tags).

• Documents with tags (the first element of the sort array is 1) are sorted first, and they are sorted internally in ascending order according to the timestamp of createdAt (1669283100000 before 1669369500000).
• Untagged documents (the first element of the sort array is 0) are sorted at the back, and they are arranged internally in descending order according to the timestamp of createdAt (-1669455900000 corresponds to 2022-11-26, -1669196700000 corresponds to 2022-11-23. Due to negative values in ascending order, -1669455900000 is smaller than -1669196700000, so the documents on 2022-11-26 are ranked before the documents on 2022-11-23, achieving descending order).

Things to note and best practices

1. Performance considerations: Script sorting is generally more expensive than field sorting because it requires executing the script on each document. For large datasets, this can lead to performance degradation. If possible, avoid complex script sorting through index design or the use of runtime fields.
2. Script caching: Elasticsearch caches compiled scripts to reduce the cost of repeated execution. Therefore, the performance will be better when the same script is executed multiple times.
3. Field types: Make sure fields accessed in the script have the correct type. For example, doc['tags.keyword'] accesses the tags field of keyword type. For a date field, doc['createdAt'].value.toInstant().toEpochMilli() is a common way to get its millisecond timestamp.
4. Painless syntax: Painless is a safe and efficient scripting language dedicated to Elasticsearch. Familiarity with its syntax and API is key to writing effective scripts.
5. Alternatives: In some simple scenarios, you can consider using missing parameters or nested fields to handle missing values ​​or sorting of array fields, but for the complex conditional logic in this example, script sorting is currently the most direct and powerful method.

Summarize

Through Painless script sorting, Elasticsearch provides extremely high flexibility to handle complex sorting requirements based on conditional judgments. Although script sorting will bring a certain performance overhead, it is an indispensable and powerful tool in scenarios where business logic cannot be implemented through standard field sorting. Understanding its working principles and best practices can help developers build search functions that meet diverse needs.

The above is the detailed content of Elasticsearch multi-field and conditional sorting: Painless script implementation guide. For more information, please follow other related articles on the PHP Chinese website!