mongodb - 用于嵌套分组的 MongoDB 查询

Question

我有 3 个收藏：

1. 计划
2. 业务流程
3. 恢复策略

他们三个将他们的主键存储在_id和

我有另一个收藏：planRecoveryStrategy哪些商店

1. planId
2. businessProcessId
3. recoveryStrategyId

存储在的示例文档planRecoveryStrategy：

{   "planId" : "PLN1",
    "processId" : "PCS1",
    "strategyId" : "RES1"
}

{   "planId" : "PLN1",
    "processId" : "PCS2",
    "strategyId" : "RES1"
}

{   "planId" : "PLN1",
    "processId" : "PCS2",
    "strategyId" : "RES2"
}

给出的查询planId是'PCS1'

{   "planId" : "PLN1",
    "processes" : [{
      "processId":"PCS1",
      "processData":{<data from Business Process Dictionary for PCS1>},
      "strategies":[{<data from Recovery Strategy Dictionary for RES1>}]
    },
    {
      "processId":"PCS2",
      "processData":{<data from Business Process Dictionary for PCS1>},
      "strategies":[{<data from Recovery Strategy Dictionary for RES1>}, 
                    {<data from Recovery Strategy Dictionary for RES2>}]
    }]
}

从planRecoveryStrategy?

标签： mongodbmongodb-query

Answer 1

尝试对数据进行分组：

db.collection.aggregate([
    {
        $group: {
            _id: {
                planId: "$planId",
                processId: "$processId"
            },
            strategies: { $addToSet: "$strategyId" }
        }
    },
    {
        $group: {
            _id: "$_id.planId",
            planId: { $first: "$_id.planId" },
            processes: {
                $addToSet: {
                    processId: "$_id.processId",
                    strategies: "$strategies"
                }
            }
        }
    }
]);

排序：

db.collection.aggregate([
    { $sort: { "strategyId": -1 } },
    { $sort: { "processId": 1 } },
    {
        $group: {
            _id: {
                planId: "$planId",
                processId: "$processId"
            },
            strategies: { $addToSet: "$strategyId" }
        }
    },
    {
        $group: {
            _id: "$_id.planId",
            planId: { $first: "$_id.planId" },
            processes: {
                $addToSet: {
                    processId: "$_id.processId",
                    strategies: "$strategies"
                }
            }
        }
    }
]);

包括其他收藏：

db.collection.aggregate([
    { $sort: { "strategyId": -1 } },
    { $sort: { "processId": 1 } },
    {
        $lookup:
          {
            from: "BusinessProcess",
            localField: "processId", 
            foreignField: "id",
            as: "BusinessProcesses"
          }
    },
    {
        $lookup:
          {
            from: "RecoveryStrategy",
            localField: "strategyId", 
            foreignField: "id",
            as: "RecoveryStrategy"
          }
    },
    {
        $group: {
            _id: {
                planId: "$planId",
                processId: "$processId",
                processData: { $arrayElemAt : ["$BusinessProcesses" , 0] },
            },
            strategies: { $addToSet: "$RecoveryStrategy" }
        }
    },
    {
        $group: {
            _id: "$_id.planId",
            planId: { $first: "$_id.planId" },
            processes: {
                $addToSet: {
                    processId: "$_id.processId",
                    processData: "$_id.processData",
                    strategies: "$strategies"
                }
            }
        }
    }
]);