MongoDB：$search内排序与普通排序序列差异及统一方案问询

MongoDB中使用$search操作内的排序，和普通$sort操作的排序序列存在差异，以下两个案例可验证该问题：

案例1（$search内排序） #

db.collection.aggregate([{
    $search: {
      index: "default_multi_sort",
      compound: {
        must: [
          {
            in: {
              path: "_id",
              value: [
               ObjectId("636cf2849d8e183db53aafca"),
               ObjectId("636cf2849d8e183db53aafcf"),
               ObjectId("636cf2849d8e183db53aafcd"),
               ObjectId("636cf2849d8e183db53aafce"),
              ],
            },
          },
        ],
      },
      sort: {
        name: 1,
      },
    },
  }], {
            collation: {
                locale: "en",
                numericOrdering: true
            }
        })

返回结果排序：数字在前、字母在后

案例2（普通$sort排序） #

db.collection.aggregate([
  {
    $match:
      {
        _id: {
          $in: [
            ObjectId("636cf2849d8e183db53aafca"),
            ObjectId("636cf2849d8e183db53aafcf"),
            ObjectId("636cf2849d8e183db53aafcd"),
            ObjectId("636cf2849d8e183db53aafce"),
          ],
        },
      },
  },
  {
    $sort: {
      name: 1,
    },
  },
], {
  collation: {
    locale: "en",
    numericOrdering: true
  }
});

返回结果排序：特殊字符在前，随后是数字，最后是字母

当前使用的搜索索引定义：

{
  "mappings": {
    "dynamic": true,
    "fields": {
      "name": [
            {
              "tokenization": "nGram",
              "type": "autocomplete",
              "analyzer": "lucene.keyword"
            },
            {
              "type": "token"
            }
          ],
        }
}

问题原因与调整方案 #

原因分析 #

$search的排序依赖搜索索引的字段配置，默认会使用token类型的子字段，其排序逻辑不匹配你指定的collation规则；而普通$sort严格遵循集合级别的collation配置，导致二者排序行为出现差异。

调整方案 #

方案1：指定$sort使用keyword类型的索引字段

修改$search的sort配置，明确使用带有lucene.keyword分析器的autocomplete子字段，保留原始字符串的排序逻辑：

db.collection.aggregate([{
    $search: {
      index: "default_multi_sort",
      compound: {
        must: [
          {
            in: {
              path: "_id",
              value: [
               ObjectId("636cf2849d8e183db53aafca"),
               ObjectId("636cf2849d8e183db53aafcf"),
               ObjectId("636cf2849d8e183db53aafcd"),
               ObjectId("636cf2849d8e183db53aafce"),
              ],
            },
          },
        ],
      },
      sort: {
        name: {
          path: "name",
          order: 1,
          type: "string",
          multi: "autocomplete"
        }
      },
    },
  }], {
            collation: {
                locale: "en",
                numericOrdering: true
            }
        })

方案2：给token字段配置匹配的collation

修改搜索索引，为token类型字段添加与集合一致的collation配置，让$search排序逻辑对齐普通$sort：

{
  "mappings": {
    "dynamic": true,
    "fields": {
      "name": [
            {
              "tokenization": "nGram",
              "type": "autocomplete",
              "analyzer": "lucene.keyword"
            },
            {
              "type": "token",
              "collation": {
                "locale": "en",
                "numericOrdering": true
              }
            }
          ],
        }
}

方案3：$search后接普通$sort阶段

如果不需要利用搜索索引的排序性能，可在$search之后添加$sort阶段，完全复用普通排序逻辑：

db.collection.aggregate([{
    $search: {
      index: "default_multi_sort",
      compound: {
        must: [
          {
            in: {
              path: "_id",
              value: [
               ObjectId("636cf2849d8e183db53aafca"),
               ObjectId("636cf2849d8e183db53aafcf"),
               ObjectId("636cf2849d8e183db53aafcd"),
               ObjectId("636cf2849d8e183db53aafce"),
              ],
            },
          },
        ],
      }
    },
  },
  {
    $sort: {
      name: 1,
    },
  }], {
            collation: {
                locale: "en",
                numericOrdering: true
            }
        })

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