如何从MongoDB集合获取按建立时间排序的双向伙伴关系列表？

我有两个MongoDB集合：

• users：存储所有用户信息
• partnership：存储用户间的关注/伙伴关系记录

需求目标：获取所有双向确认的伙伴关系，并按伙伴关系建立时间排序。
伙伴关系成立条件：当user_1点赞user_2，且user_2也点赞user_1时（双向点赞），双方成为伙伴。
伙伴关系建立时间：第二次点赞（即完成双向确认）的时间。

样例数据 #

db={
  partnership: [
    {
      _id: "xyz_rrr",
      updated: "2022-10-23T12:35:24.772+00:00",
      users: [
        "xyz",
        "rrr"
      ]
    },
    {
      _id: "rrr_eee",
      updated: "2022-12-23T12:35:24.772+00:00",
      users: [
        "rrr",
        "eee"
      ]
    },
    {
      _id: "eee_rrr",
      updated: "2023-01-21T12:35:24.772+00:00",
      users: [
        "eee",
        "rrr"
      ]
    },
    {
      _id: "mmm_rrr",
      updated: "2023-02-19T12:35:24.772+00:00",
      users: [
        "mmm",
        "rrr"
      ]
    },
    {
      _id: "rrr_mmm",
      updated: "2023-02-21T12:35:24.772+00:00",
      users: [
        "rrr",
        "mmm"
      ]
    },
    
  ],
  users: [
    {
      _id: "abc",
      name: "abc",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          53.23,
          67.12
        ]
      },
      calculatedDist: 112
    },
    {
      _id: "xyz",
      name: "xyyy",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          54.23,
          67.12
        ]
      },
      calculatedDist: 13
    },
    {
      _id: "123",
      name: "yyy",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          54.23,
          67.12
        ]
      },
      calculatedDist: 13
    },
    {
      _id: "rrr",
      name: "rrrrrrr",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          51.23,
          64.12
        ]
      },
      calculatedDist: 14
    },
    {
      _id: "mmm",
      name: "mmmm",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          51.23,
          64.12
        ]
      },
      calculatedDist: 14
    },
    {
      _id: "eee",
      name: "eeeee",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          55.23,
          62.12
        ]
      },
      calculatedDist: 143
    }
  ],
  
}

预期结果 #

[
  {
    "partneredUsers": {
      "firstUser": {
        "_id": "mmm",
        "name": "mmmm"
      },
      "secondUser": {
        "_id": "rrr",
        "name": "rrrrrrr"
      }
    },
    "partneredDate": "2023-02-21T12:35:24.772+00:00"
  },
  {
    "partneredUsers": {
      "firstUser": {
        "_id": "rrr",
        "name": "rrrrrrr"
      },
      "secondUser": {
        "_id": "eee",
        "name": "eeeee"
      }
    },
    "partneredDate": "2023-01-21T12:35:24.772+00:00"
  }
]

解决方案：MongoDB聚合查询 #

db.partnership.aggregate([
  // 标准化用户对：按_id排序，避免[a,b]和[b,a]被视为不同组
  {
    $addFields: {
      sortedUsers: { $sortArray: { input: "$users", sortBy: 1 } }
    }
  },
  // 按标准化用户对分组，筛选出有双向记录的组
  {
    $group: {
      _id: "$sortedUsers",
      records: { $push: { updated: "$updated", users: "$users" } },
      count: { $sum: 1 }
    }
  },
  { $match: { count: 2 } },
  // 获取双向确认的时间（即最晚的点赞时间）
  {
    $addFields: {
      partneredDate: { $max: "$records.updated" },
      userPair: { $first: "$_id" }
    }
  },
  // 关联users集合获取双方用户信息
  {
    $lookup: {
      from: "users",
      localField: "userPair.0",
      foreignField: "_id",
      as: "firstUser"
    }
  },
  {
    $lookup: {
      from: "users",
      localField: "userPair.1",
      foreignField: "_id",
      as: "secondUser"
    }
  },
  // 格式化输出结构
  {
    $project: {
      _id: 0,
      partneredUsers: {
        firstUser: {
          _id: { $first: "$firstUser._id" },
          name: { $first: "$firstUser.name" }
        },
        secondUser: {
          _id: { $first: "$secondUser._id" },
          name: { $first: "$secondUser.name" }
        }
      },
      partneredDate: 1
    }
  },
  // 按伙伴关系建立时间倒序排序
  { $sort: { partneredDate: -1 } }
])

查询说明 #

1. 标准化用户对：通过$sortArray统一用户对的排序规则，确保双向点赞记录被归为同一组。
2. 筛选双向记录：分组后只保留计数为2的组，即存在双向点赞的伙伴关系。
3. 确认建立时间：取每组中最晚的updated时间，作为伙伴关系正式建立的时间。
4. 关联用户信息：通过两次$lookup从users集合拉取双方的基础信息。
5. 格式调整与排序：整理输出结构匹配预期结果，最后按建立时间倒序排列。

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