如何在Elasticsearch/FOSElasticaBundle按嵌套对象日期差排序？

实体结构与示例数据 #

我有两个实体：file及其子实体tasks，示例JSON数据如下：

[{
    "id": 1,
    "name": "file1",
    "tasks": [{
        "id": 1,
        "targetDate": "2023-06-01"
      },{
        "id": 2,
        "targetDate": "2020-07-01"
      }]
  },{
    "id": 2,
    "name": "file2",
    "tasks": [{
        "id": 3,
        "targetDate": "2022-06-01"
      },{
        "id": 4,
        "targetDate": "2023-06-27"
      }]
  }]

排序需求 #

按每个file中最接近当前日期的task对结果排序，因此file2应排在首位，因其包含的2023-06-27日期最接近今日。

对应的SQL参考示例 #

SELECT f.id, f.name,
       DATEDIFF(CURRENT_DATE, MIN(t.target_date)) AS date_difference
FROM files f
INNER JOIN tasks t on t.file_id = f.id
GROUP BY f.id
ORDER BY date_difference DESC

技术目标 #

需要在Symfony的FOSElasticaBundle中实现该功能，也可接受纯Elasticsearch查询请求示例。已查阅脚本示例、聚合及嵌套排序相关内容，但不清楚如何构建查询。

已尝试的脚本（存在问题） #

已写出计算日期差的脚本，但无法对script_fields进行排序。目前尝试将逻辑写入排序脚本，但遇到嵌套字段相关问题，当前脚本如下：

{
  "sort": [{
    "_script": {
        "type": "number",
        "order": "desc",
        "nested": {
            "path": "taken",
            "script": {}
        }
    }
  }],
  "query": {
    "bool": {
      "filter": [{
        "nested": {
          "path": "taken",
          "inner_hits": {
              "script_fields": {
                  "diff_dates": {
                      "script": {
                          "source": "Instant instant = Instant.ofEpochMilli(params.current_date); ZonedDateTime now = ZonedDateTime.ofInstant(instant, ZoneId.of('Z')); return doc['taken.targetDate'].value.until(now, ChronoUnit.DAYS);",
                          "params": {
                              "current_date": 1688031608000
                          }
                      }
                  }
              }
          },
          "query": {
            "bool": {
              "filter": [{
                "script": {
                  "script": "doc['taken.status'].value != 'Open'"
                }
              }]
            }
          }
        }
      }]
    }
  }
}

解决方案 #

纯Elasticsearch查询实现 #

要实现按每个file中最接近当前日期的task排序，需使用嵌套排序+脚本，核心逻辑是计算每个task的targetDate与当前日期的差值绝对值，取最小的差值作为排序依据，差值越小（日期越近）的file越靠前。

修正后的查询（注意：将原脚本中的taken修正为示例对应的tasks字段）：

{
  "query": {
    "bool": {
      "filter": [
        {
          "nested": {
            "path": "tasks",
            "query": {
              "bool": {
                "filter": [
                  {
                    "script": {
                      "source": "doc['tasks.status'].value != 'Open'"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "sort": [
    {
      "_script": {
        "type": "number",
        "order": "asc",
        "script": {
          "source": """
            def minDiff = Long.MAX_VALUE;
            def now = Instant.ofEpochMilli(params.current_date);
            for (def date : doc['tasks.targetDate']) {
              def diff = Math.abs(date.value.toInstant().toEpochMilli() - now.toEpochMilli());
              if (diff < minDiff) {
                minDiff = diff;
              }
            }
            return minDiff;
          """,
          "params": {
            "current_date": 1688031608000
          }
        }
      }
    }
  ]
}

说明：

• 脚本遍历当前file的所有tasks.targetDate，计算每个日期与当前日期的毫秒差绝对值，找出最小差值
• 按最小差值升序排序，确保最近日期的file排在前面
• 若要完全匹配SQL示例的date_difference逻辑，可去掉绝对值，直接计算date.value.toInstant().toEpochMilli() - now.toEpochMilli()，再取最小值按降序排序

FOSElasticaBundle实现方式 #

1. 查询构建器动态添加排序

use Elastica\Query;
use Elastica\Script;
use Elastica\Sort;

// 构建基础查询
$query = new Query();

// 添加嵌套过滤条件
$nestedFilter = new Query\Nested();
$nestedFilter->setPath('tasks');
$statusScript = new Script("doc['tasks.status'].value != 'Open'");
$boolFilter = new Query\BoolQuery();
$boolFilter->addFilter(new Query\Script($statusScript));
$nestedFilter->setQuery($boolFilter);

$mainBool = new Query\BoolQuery();
$mainBool->addFilter($nestedFilter);
$query->setQuery($mainBool);

// 编写排序脚本
$scriptSource = <<<SCRIPT
def minDiff = Long.MAX_VALUE;
def now = Instant.ofEpochMilli(params.current_date);
for (def date : doc['tasks.targetDate']) {
  def diff = Math.abs(date.value.toInstant().toEpochMilli() - now.toEpochMilli());
  if (diff < minDiff) {
    minDiff = diff;
  }
}
return minDiff;
SCRIPT;

$script = new Script($scriptSource);
$script->setParams(['current_date' => time() * 1000]); // 当前时间转毫秒

// 添加排序规则
$sort = new Sort\ScriptSort($script, 'number');
$sort->setOrder('asc');
$query->addSort($sort);

// 执行查询
$results = $this->get('fos_elastica.index.your_index.file')->search($query);

2. 索引配置中定义固定排序

在config/packages/fos_elastica.yaml的对应类型配置中添加排序：

fos_elastica:
    indexes:
        your_index:
            types:
                file:
                    mappings:
                        id: ~
                        name: ~
                        tasks:
                            type: nested
                            properties:
                                id: ~
                                targetDate:
                                    type: date
                                    format: "yyyy-MM-dd"
                                status: ~
                    persistence:
                        # 你的持久化配置...
                    sort:
                        - _script:
                            type: number
                            order: asc
                            script:
                                source: |
                                    def minDiff = Long.MAX_VALUE;
                                    def now = Instant.ofEpochMilli(params.current_date);
                                    for (def date : doc['tasks.targetDate']) {
                                      def diff = Math.abs(date.value.toInstant().toEpochMilli() - now.toEpochMilli());
                                      if (diff < minDiff) {
                                        minDiff = diff;
                                      }
                                    }
                                    return minDiff;
                                params:
                                    current_date: 1688031608000 # 可动态传入当前时间

关键注意事项 #

• 确保tasks字段在Elasticsearch中被映射为nested类型，否则无法正确遍历嵌套数组
• 脚本中使用的日期字段名需与映射配置一致
• 可根据需求调整排序逻辑：若需优先显示未过期的最近日期，可在脚本中加入日期前后判断

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