Spring Data MongoDB：合并两个字段作为统一排序依据的实现

问题背景 #

实体类定义 #

@Document
public class Equipment
{
    // ... 更多属性及getter/setter
    private String name;
    private String internalManufacturer;
    private String externalManufacturer;
    // ... 更多属性及getter/setter
}

现有排序逻辑缺陷 #

现有服务通过字段编码配置排序，处理制造商排序时，会先按internalManufacturer排序，再按externalManufacturer排序，无法实现将两个字段视为统一排序依据的需求：

private void setSorting(String code)
{
    // ... 其他逻辑
    List<AggregationOperation> pagedAndSortedQuery= new ArrayList<>(baseQuery);
    SortOperation sortOperation = sort(Sort.Direction.ASC, code);
    if (EquipmentsSortingField.MANUFACTURER.getCode().equals(code))
    {
        sortOperation = sort(sortingDirection, "internalManufacturer", "externalManufacturer");
        pagedAndSortedQuery.add(sortOperation);
    }
    // ... 其他逻辑
}

数据与排序预期 #

已知internalManufacturer和externalManufacturer互斥（一个为空时另一个非空），现有数据：

Equipment (externalManufacturer: John, internalManufacturer: null)
Equipment (externalManufacturer: null, internalManufacturer: Carlos)
Equipment (externalManufacturer: Ash, internalManufacturer: null)
Equipment (externalManufacturer: null, internalManufacturer: Victor)

当前排序结果（不符合预期）：

Equipment (externalManufacturer: null, internalManufacturer: Carlos)
Equipment (externalManufacturer: null, internalManufacturer: Victor)
Equipment (externalManufacturer: Ash, internalManufacturer: null)
Equipment (externalManufacturer: John, internalManufacturer: null)

期望排序结果（按合并后的制造商名称统一排序）：

Equipment (externalManufacturer: Ash, internalManufacturer: null)
Equipment (externalManufacturer: null, internalManufacturer: Carlos)
Equipment (externalManufacturer: John, internalManufacturer: null)
Equipment (externalManufacturer: null, internalManufacturer: Victor)

解决方案 #

核心思路是利用MongoDB聚合操作，先将两个互斥字段合并为一个虚拟字段，再按该虚拟字段排序。

修改后的排序逻辑代码 #

private void setSorting(String code)
{
    // ... 其他逻辑
    List<AggregationOperation> pagedAndSortedQuery= new ArrayList<>(baseQuery);
    SortOperation sortOperation = sort(Sort.Direction.ASC, code);
    
    if (EquipmentsSortingField.MANUFACTURER.getCode().equals(code)) {
        // 1. 投影操作：合并两个字段为虚拟字段manufacturer
        ProjectionOperation projection = project()
                .andExpression("coalesce(internalManufacturer, externalManufacturer)").as("manufacturer")
                // 保留所有原有字段，避免业务数据丢失
                .andExclude("_id").andInclude("*");
        
        // 2. 按合并后的虚拟字段排序
        sortOperation = sort(sortingDirection, "manufacturer");
        
        // 3. 将投影和排序操作加入聚合查询列表
        pagedAndSortedQuery.add(projection);
        pagedAndSortedQuery.add(sortOperation);
    }
    // ... 其他逻辑
}

代码说明 #

• $coalesce操作符会返回第一个非空的字段值，完美适配两个字段互斥的场景，将internalManufacturer和externalManufacturer合并为统一的manufacturer虚拟字段。
• 投影时使用andInclude("*")保留所有原有字段，确保后续业务逻辑能正常获取完整的Equipment数据。
• 最终按合并后的manufacturer字段排序，即可实现统一排序的需求。

兼容低版本MongoDB：若使用MongoDB 3.2及以下版本（不支持$coalesce），可替换为$ifNull实现相同效果：

ProjectionOperation projection = project()
        .andExpression("ifNull(internalManufacturer, externalManufacturer)").as("manufacturer")
        .andExclude("_id").andInclude("*");

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