You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

在MongoDB深层嵌套对象数组中实现Lookup关联查询

avatar
云原生阿岚
2026-6-28
MongoDB聚合查询：为嵌套评论关联用户详情

已实现工单聚合查询中ticketLogs相关字段的关联查询（用户、状态、部门等），现需为每个ticketLog下comments数组中comment对象的commentedBy字段关联用户详情，展示评论者信息。

当前查询代码 

const result = await this.ticketsv2Model
  .aggregate([
    {
      $match: query,
    },
    {
      $sort: {
        createdAt: -1,
      },
    },
    ...generateLookupStage('issues', 'issueId', ['_id', 'name']),
    ...generateLookupStage('subissues', 'subIssueId', ['_id', 'name']),
    ...generateLookupStage('tickettypes', 'typeId', ['_id', 'name']),
    ...generateLookupStage('statustypes', 'statusId', ['_id', 'name']),
    ...generateLookupStage('departments', 'assignedDepartment', [
      '_id',
      'name',
    ]),
    ...generateLookupStage('users', 'assignedTo', [
      '_id',
      'extension',
      'firstName',
      'lastName',
    ]),
    ...generateLookupStage('users', 'openedBy', [
      '_id',
      'extension',
      'firstName',
      'lastName',
    ]),

    {
      $unwind: '$ticketLogs',
    },
    ...generateLookupStage('users', 'ticketLogs.userId', [
      '_id',
      'firstName',
      'lastName',
      'extension',
    ]),
    ...generateLookupStage('statustypes', 'ticketLogs.statusId', [
      '_id',
      'name',
    ]),

    ...generateLookupStage(
      'departments',
      'ticketLogs.assignedDepartment',
      ['_id', 'name', 'number'],
    ),
    ...generateLookupStage('users', 'ticketLogs.assignedTo', [
      '_id',
      'firstName',
      'lastName',
      'extension',
    ]),
    {
      $group: {
        _id: '$_id',
        issueId: { $first: '$issueId' },
        subIssueId: { $first: '$subIssueId' },
        typeId: { $first: '$typeId' },
        ticketId: { $first: '$ticketId' },
        resolutionTime: { $first: '$resolutionTime' },
        clientInfo: { $first: '$clientInfo' },
        statusId: { $first: '$statusId' },
        openedBy: { $first: '$openedBy' },
        remarks: { $first: '$remarks' },
        assignedTo: { $first: '$assignedTo' },
        assignedDepartment: { $first: '$assignedDepartment' },
        ticketLogs: {
          $push: {
            chatId: '$ticketLogs.chatId',
            recordingId: '$ticketLogs.recordingId',
            voiceMailRecordingId: '$ticketLogs.voiceMailRecordingId',
            remarks: '$ticketLogs.remarks',
            statusId: '$ticketLogs.statusId',
            userId: '$ticketLogs.userId',
            assignedTo: '$ticketLogs.assignedTo',
            assignedDepartment: '$ticketLogs.assignedDepartment',
            comments: '$ticketLogs.comments',
          },
        },
      },
    },
    {
      $project: {
        createdAt: 1,
        issueId: 1,
        subIssueId: 1,
        typeId: 1,
        clientInfo: 1,
        statusId: 1,
        remarks: 1,
        assignedDepartment: 1,
        assignedTo: 1,
        ticketLogs: 1,
        ticketId: 1,
        openedBy: 1,
        _id: 1,
        action: {
          $switch: {
            branches: [
              {
                case: {
                  $eq: ['$assignedTo._id', new Types.ObjectId(user)],
                },
                then: 'update',
              },
              {
                case: {
                  $and: [
                    {
                      $in: ['$assignedDepartment._id', userDepartments],
                    },
                    { $eq: [{ $type: '$assignedTo._id' }, 'missing'] },
                  ],
                },
                then: 'pick',
              },
            ],
            default: 'view',
          },
        },
      },
    },
  ])
  .exec();

自定义函数说明

generateLookupStage是自定义聚合阶段生成函数，参数依次为：

  • 关联集合名称
  • 本地关联字段路径
  • 需要返回的关联文档字段列表

示例工单文档 

{
  "_id": {
    "$oid": "65b2143e5b239b78bc756a68"
  },
  "issueId": {
    "$oid": "644e940200d3bd6ab311f1fb"
  },
  "subIssueId": {
    "$oid": "644e94b100d3bd6ab311f216"
  },
  "typeId": {
    "$oid": "65828ebea1726254da22e506"
  },
  "clientInfo": {
    "clientId": "1231235645123",
    "clientEmail": "arpan@arpan.com",
    "clientPhone": "12233",
    "clientName": "Arpan Dhakal",
    "clientImage": "linkto.image",
    "_id": {
      "$oid": "65b2143e5b239b78bc756a69"
    }
  },
  "statusId": {
    "$oid": "658294ca9777467f6777802e"
  },
  "remarks": "Custom remarks here",
  "assignedDepartment": {
    "$oid": "658294a2aa8146af8fe85b50"
  },
  "ticketLogs": [
    {
      "chatId": "658294a2aa8146af8fe85b50",
      "recordingId": "658294a2aa8146af8fe85b50",
      "voiceMailRecordingId": "658294a2aa8146af8fe85b50",
      "remarks": "Ticket Created",
      "statusId": {
        "$oid": "658294f1660cac3e3acda29a"
      },
      "userId": {
        "$oid": "65af5d2e08a356fd82718d64"
      },
      "assignedTo": null,
      "assignedDepartment": {
        "$oid": "658294a2aa8146af8fe85b50"
      },
      "_id": {
        "$oid": "65b2143e5b239b78bc756a6a"
      },
      "createdAt": {
        "$date": "2024-01-25T07:56:46.778Z"
      },
      "updatedAt": {
        "$date": "2024-01-25T07:56:46.778Z"
      },
      "comments": [
        {
          "comment": "hello world",
          "createdAt": {
            "$date": "2024-01-26T06:21:21.429Z"
          },
          "commentedBy": {
            "$oid": "65af5d2e08a356fd82718d64"
          },
          "_id": {
            "$oid": "65b34f61ec6cb747fc19f73a"
          }
        },
        {
          "comment": "hello world",
          "createdAt": {
            "$date": "2024-01-26T06:30:14.002Z"
          },
          "commentedBy": {
            "$oid": "65af5d2e08a356fd82718d64"
          },
          "_id": {
            "$oid": "65b35176cb2f23c9563c45f5"
          }
        },
        {
          "comment": "comment",
          "commentedBy": {
            "$oid": "644e06fc00d3bd6ab311d5fd"
          },
          "_id": {
            "$oid": "65e99131c599c2d633062497"
          },
          "createdAt": {
            "$date": "2024-03-07T10:04:33.007Z"
          },
          "updatedAt": {
            "$date": "2024-03-07T10:04:33.007Z"
          }
        }
      ]
    }
  ],
  "ticketId": "T-55NNXUBW",
  "openedBy": {
    "$oid": "644e06fc00d3bd6ab311d5fd"
  },
  "createdAt": {
    "$date": "2024-01-25T07:56:46.778Z"
  },
  "updatedAt": {
    "$date": "2024-03-07T10:04:33.007Z"
  },
  "__v": 0,
  "assignedTo": {
    "$oid": "644e06fc00d3bd6ab311d5fd"
  }
}

解决方案

要实现评论者信息关联，需在$unwind: '$ticketLogs'之后、原$group之前，添加展开评论、关联用户、重组数组的步骤，最终恢复原文档结构。

修改后的完整查询代码 

const result = await this.ticketsv2Model
  .aggregate([
    {
      $match: query,
    },
    {
      $sort: {
        createdAt: -1,
      },
    },
    ...generateLookupStage('issues', 'issueId', ['_id', 'name']),
    ...generateLookupStage('subissues', 'subIssueId', ['_id', 'name']),
    ...generateLookupStage('tickettypes', 'typeId', ['_id', 'name']),
    ...generateLookupStage('statustypes', 'statusId', ['_id', 'name']),
    ...generateLookupStage('departments', 'assignedDepartment', [
      '_id',
      'name',
    ]),
    ...generateLookupStage('users', 'assignedTo', [
      '_id',
      'extension',
      'firstName',
      'lastName',
    ]),
    ...generateLookupStage('users', 'openedBy', [
      '_id',
      'extension',
      'firstName',
      'lastName',
    ]),

    {
      $unwind: '$ticketLogs',
    },
    ...generateLookupStage('users', 'ticketLogs.userId', [
      '_id',
      'firstName',
      'lastName',
      'extension',
    ]),
    ...generateLookupStage('statustypes', 'ticketLogs.statusId', [
      '_id',
      'name',
    ]),

    ...generateLookupStage(
      'departments',
      'ticketLogs.assignedDepartment',
      ['_id', 'name', 'number'],
    ),
    ...generateLookupStage('users', 'ticketLogs.assignedTo', [
      '_id',
      'firstName',
      'lastName',
      'extension',
    ]),

    // 新增：展开comments数组，保留无评论的日志
    {
      $unwind: {
        path: '$ticketLogs.comments',
        preserveNullAndEmptyArrays: true
      }
    },
    // 新增：关联评论者用户信息
    ...generateLookupStage('users', 'ticketLogs.comments.commentedBy', [
      '_id',
      'firstName',
      'lastName',
      'extension'
    ]),
    // 新增：替换commentedBy为关联的用户详情
    {
      $addFields: {
        'ticketLogs.comments.commentedBy': {
          $ifNull: ['$ticketLogs.comments.commentedBy', null]
        }
      }
    },
    // 新增：重组comments数组到对应ticketLog
    {
      $group: {
        _id: {
          ticketId: '$_id',
          ticketLogId: '$ticketLogs._id'
        },
        issueId: { $first: '$issueId' },
        subIssueId: { $first: '$subIssueId' },
        typeId: { $first: '$typeId' },
        ticketId: { $first: '$ticketId' },
        resolutionTime: { $first: '$resolutionTime' },
        clientInfo: { $first: '$clientInfo' },
        statusId: { $first: '$statusId' },
        openedBy: { $first: '$openedBy' },
        remarks: { $first: '$remarks' },
        assignedTo: { $first: '$assignedTo' },
        assignedDepartment: { $first: '$assignedDepartment' },
        ticketLog: {
          $first: {
            chatId: '$ticketLogs.chatId',
            recordingId: '$ticketLogs.recordingId',
            voiceMailRecordingId: '$ticketLogs.voiceMailRecordingId',
            remarks: '$ticketLogs.remarks',
            statusId: '$ticketLogs.statusId',
            userId: '$ticketLogs.userId',
            assignedTo: '$ticketLogs.assignedTo',
            assignedDepartment: '$ticketLogs.assignedDepartment',
            _id: '$ticketLogs._id',
            createdAt: '$ticketLogs.createdAt',
            updatedAt: '$ticketLogs.updatedAt'
          }
        },
        comments: { $push: '$ticketLogs.comments' }
      }
    },
    // 新增：将comments合并到ticketLog
    {
      $addFields: {
        'ticketLog.comments': '$comments'
      }
    },
    // 新增：重组ticketLogs数组到原工单
    {
      $group: {
        _id: '$_id.ticketId',
        issueId: { $first: '$issueId' },
        subIssueId: { $first: '$subIssueId' },
        typeId: { $first: '$typeId' },
        ticketId: { $first: '$ticketId' },
        resolutionTime: { $first: '$resolutionTime' },
        clientInfo: { $first: '$clientInfo' },
        statusId: { $first: '$statusId' },
        openedBy: { $first: '$openedBy' },
        remarks: { $first: '$remarks' },
        assignedTo: { $first: '$assignedTo' },
        assignedDepartment: { $first: '$assignedDepartment' },
        ticketLogs: { $push: '$ticketLog' }
      }
    },

    {
      $project: {
        createdAt: 1,
        issueId: 1,
        subIssueId: 1,
        typeId: 1,
        clientInfo: 1,
        statusId: 1,
        remarks: 1,
        assignedDepartment: 1,
        assignedTo: 1,
        ticketLogs: 1,
        ticketId: 1,
        openedBy: 1,
        _id: 1,
        action: {
          $switch: {
            branches: [
              {
                case: {
                  $eq: ['$assignedTo._id', new Types.ObjectId(user)],
                },
                then: 'update',
              },
              {
                case: {
                  $and: [
                    {
                      $in: ['$assignedDepartment._id', userDepartments],
                    },
                    { $eq: [{ $type: '$assignedTo._id' }, 'missing'] },
                  ],
                },
                then: 'pick',
              },
            ],
            default: 'view',
          },
        },
      },
    },
  ])
  .exec();

关键修改说明

  • 用$unwind展开ticketLogs.comments，通过preserveNullAndEmptyArrays保留无评论的日志条目
  • 调用generateLookupStage关联users集合，获取评论者的详细信息
  • 通过两次$group操作分别重组comments
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.28 09:45:57