基于嵌套聚合结果筛选Elasticsearch中1月1-10日可用轮椅的商家

Alright, let's build the query step by step to meet your requirements. We need to first target items related to wheelchairs, then calculate remaining stock after accounting for reservations in the specified date range.

The Query #

GET items/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "wheelchair",
            "fields": ["title", "fullDescription"]
          }
        }
      ],
      "filter": [
        {
          "script": {
            "script": {
              "source": """
                long totalReserved = 0;
                // Iterate through all reservations for the item
                for (def reservation : params._source.reservations) {
                  // Check if the reservation overlaps with Jan 1-10, 2024
                  def targetStart = ZonedDateTime.parse('2023-12-31T23:59:59Z').toInstant();
                  def targetEnd = ZonedDateTime.parse('2024-01-11T00:00:00Z').toInstant();
                  
                  def resStart = reservation.startDate.toInstant();
                  def resEnd = reservation.endDate.toInstant();
                  
                  // Reservation overlaps if it starts before our target end and ends after our target start
                  if (resStart.isBefore(targetEnd) && resEnd.isAfter(targetStart)) {
                    totalReserved += reservation.quantity;
                  }
                }
                // Return true only if remaining stock is greater than 0
                return params._source.quantity - totalReserved > 0;
              """,
              "lang": "painless"
            }
          }
        }
      ]
    }
  }
}

How It Works #

1. Filter Wheelchair-Related Items: The multi_match clause searches for the keyword "wheelchair" in either the title or fullDescription fields, narrowing down our dataset to relevant items.
2. Calculate Remaining Stock: The Painless script does the heavy lifting:
   • It loops through each reservation attached to the item.
   • For each reservation, it checks if the reservation period overlaps with January 1st to 10th, 2024 (adjust the date strings if you need a different year).
   • It sums up the total quantity reserved in overlapping periods.
   • Finally, it compares the item's total stock (quantity) minus the reserved quantity—if the result is greater than 0, the item is included in the results.

Key Notes #

• Date Adjustments: Replace the date strings (2023-12-31T23:59:59Z and 2024-01-11T00:00:00Z) with your target year's equivalent to cover the Jan 1-10 window.
• Performance: Scripted filters can be slower on large datasets. If you run into performance issues, consider precomputing available stock for common date ranges during indexing, or using nested aggregations to pre-calculate reserved quantities.
• Date Format Compatibility: Ensure the date parsing in the script matches your index's date_optional_time format—ISO 8601 dates (like the ones used here) should work out of the box.

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