如何在Elasticsearch中按两个日期的较早值排序？

Got it, let's break down how to sort your documents based on the older date between created_at and published_on—since Elasticsearch works differently from PostgreSQL, we have two solid approaches depending on your use case.

Option 1: Use Runtime Fields (No Index Changes Needed) #

If you don't want to modify your existing index or reindex data, runtime fields are perfect. They calculate the earliest date dynamically when you run your search query.

Here's a complete example query:

GET /your_index_name/_search
{
  "runtime_mappings": {
    "earliest_date": {
      "type": "date",
      "script": """
        // Grab the values of both date fields
        def created = doc['created_at'].value;
        def published = doc['published_on'].value;

        // Handle null cases (in case one or both fields are missing)
        if (created == null && published == null) {
          emit(null); // No valid dates, skip sorting for this doc
        } else if (created == null) {
          emit(published); // Use published_on if created_at is missing
        } else if (published == null) {
          emit(created); // Use created_at if published_on is missing
        } else {
          // Pick the earlier of the two valid dates
          emit(created.isBefore(published) ? created : published);
        }
      """
    }
  },
  "sort": [
    { "earliest_date": "asc" } // Use "desc" if you want newest first
  ],
  "_source": ["created_at", "published_on", "earliest_date"] // Optional: include the computed field in results
}

How this works: #

• We define a temporary earliest_date field that calculates the older date for each document.
• The script handles all edge cases (missing fields, both fields null) to avoid errors.
• We then sort directly on this computed earliest_date field.

Option 2: Precompute with a Scripted Field (Better for Performance) #

If you need this sorting frequently, precomputing the earliest_date during indexing will be faster (since we don't calculate it on every query). You can add this field to your index mapping:

First, update your index mapping:

PUT /your_index_name/_mapping
{
  "properties": {
    "created_at": { "type": "date" },
    "published_on": { "type": "date" },
    "earliest_date": {
      "type": "date",
      "script": {
        "source": """
          def created = doc['created_at'].value;
          def published = doc['published_on'].value;

          if (created == null && published == null) {
            return null;
          } else if (created == null) {
            return published;
          } else if (published == null) {
            return created;
          } else {
            return created.isBefore(published) ? created : published;
          }
        """
      }
    }
  }
}

After updating the mapping, you'll need to reindex your existing documents so the earliest_date field is populated for old data. Once that's done, sorting becomes simple:

GET /your_index_name/_search
{
  "sort": [
    { "earliest_date": "asc" }
  ]
}

Key Notes: #

• Make sure your created_at and published_on fields are properly mapped as date types (otherwise the script will throw errors).
• For runtime fields, keep in mind they add some overhead to each query—use this for one-off or infrequent sorting needs.
• Scripted fields are better for long-term use since the value is stored with the document, making queries faster.

内容的提问来源于stack exchange，提问作者Erik Jacobs