MongoDB 6.0中$sortArray聚合操作无输出问题求助

在MongoDB 6.0的聚合查询中，使用$sortArray的$project阶段执行后完全无输出，但移除排序逻辑后查询正常运行。

问题代码对比 #

无输出的代码段 #

$project: {
  _id: 0,
  games: {
    $sortArray: {
      input: '$games',
      sortBy: { date: -1 }
    }
  },
  total: { $size: '$games' }
}

可正常运行的代码段 #

$project: {
  _id: 0,
  games: 1,
  total: { $size: '$games' }
}

完整聚合管道代码 #

const pipeline = [
  {
    $match: { _id: ObjectID( user_id ) }
  },
  {
    $lookup: {
      from: 'game',
      localField: '_id',
      foreignField: 'player_id',
      pipeline: pipelineFilters,
      as: 'owned_games'
    }
  },
  {
    $lookup: {
      from: 'viewers',
      pipeline: [
        { $match: { email: user_email } },
        {
          $lookup: {
            from: 'games',
            localField: 'game_id',
            foreignField: '_id',
            pipeline: pipelineFilters,
            as: 'games'
          }
        },
        {
          $project: {
            game: { $arrayElemAt: [ '$games', 0 ] }
          }
        },
        {
          $replaceRoot: {
            newRoot: '$game'
          }
        }
      ],
      as: 'viewing_games'
    }
  },
  {
    $project: {
      games: {
        $concatArrays: [ '$viewing_games', '$owned_games' ]
      }
    }
  },
  {
    $project: {
      _id: 0,
      games: {
        $sortArray: {
          input: '$games',
          sortBy: { date: -1 }
        }
      },
      total: { $size: '$games' }
    }
  }
];

最终$project阶段前的文档结构示例 #

{
  _id: new ObjectId("6359ac2149c98388770fb2b3"),
  games: [
    {
      _id: new ObjectId("63595544435af1b923d1bda1"),
      name: 'game 1',
      owner_id: new ObjectId("63595544435af1b923d1bd98"),
      date: 2022-10-26T15:41:56.584Z,
      status: 'draft',
      createdAt: 2022-10-26T15:41:56.599Z,
      updatedAt: 2022-10-26T15:41:56.599Z,
      __v: 0
    },
    {
      _id: new ObjectId("63595544435af1b923d1bd99"),
      name: 'game 2',
      owner_id: new ObjectId("63595544435af1b923d1bd8b"),
      date: 2011-10-05T14:48:00.000Z,
      status: 'draft',
      createdAt: 2022-10-26T15:41:56.585Z,
      updatedAt: 2022-10-26T15:41:56.585Z,
      __v: 0
    },
    {
      _id: new ObjectId("63595544435af1b923d1bd9b"),
      name: 'game 3',
      owner_id: new ObjectId("63595544435af1b923d1bd8b"),
      date: 1990-01-01T01:22:00.000Z,
      status: 'draft',
      createdAt: 2022-10-26T15:41:56.588Z,
      updatedAt: 2022-10-26T15:41:56.588Z,
      __v: 0
    },
    {
      _id: new ObjectId("63595544435af1b923d1bd9d"),
      name: 'game 4',
      owner_id: new ObjectId("63595544435af1b923d1bd8b"),
      date: 2500-10-05T14:48:00.000Z,
      status: 'draft',
      createdAt: 2022-10-26T15:41:56.592Z,
      updatedAt: 2022-10-26T15:41:56.592Z,
      __v: 0
    },
    {
      _id: new ObjectId("63595544435af1b923d1bd9f"),
      name: 'game 5',
      owner_id: new ObjectId("63595544435af1b923d1bd8b"),
      date: 1995-12-25T01:22:00.000Z,
      status: 'draft',
      createdAt: 2022-10-26T15:41:56.595Z,
      updatedAt: 2022-10-26T15:41:56.595Z,
      __v: 0
    }
  ]
}

问题定位与解决方案 #

核心问题推测

1. 数组混入null元素：第二个$lookup的子管道中，若$arrayElemAt: ['$games', 0]返回null（比如关联的games数组为空），后续$replaceRoot会将该文档转为null，最终viewing_games数组中会存在null值。$sortArray处理包含null的数组时，因null无date字段会引发异常，导致整个聚合无输出。
2. date字段异常：若games数组中部分元素缺失date字段，或字段类型混合（如部分是字符串、部分是Date对象），$sortArray排序时会触发错误，中断管道执行。

修复方案

方案1：过滤数组中的null元素

在合并数组时先过滤掉null值：

{
  $project: {
    games: {
      $filter: {
        input: { $concatArrays: ['$viewing_games', '$owned_games'] },
        cond: { $ne: ['$$this', null] }
      }
    }
  }
}

方案2：子管道中避免生成null文档

修改第二个$lookup的子管道，在$replaceRoot前过滤掉game为null的文档：

{
  $lookup: {
    from: 'viewers',
    pipeline: [
      { $match: { email: user_email } },
      {
        $lookup: {
          from: 'games',
          localField: 'game_id',
          foreignField: '_id',
          pipeline: pipelineFilters,
          as: 'games'
        }
      },
      {
        $project: {
          game: { $arrayElemAt: [ '$games', 0 ] }
        }
      },
      // 新增过滤逻辑
      { $match: { game: { $ne: null } } },
      {
        $replaceRoot: {
          newRoot: '$game'
        }
      }
    ],
    as: 'viewing_games'
  }
}

方案3：拆分排序与统计步骤

将$sortArray和$size的计算拆分到不同阶段，避免同一$project中字段计算的潜在冲突：

{
  $addFields: {
    sorted_games: {
      $sortArray: {
        input: '$games',
        sortBy: { date: -1 }
      }
    }
  }
},
{
  $project: {
    _id: 0,
    games: '$sorted_games',
    total: { $size: '$games' }
  }
}

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