MongoDB Query For Nested Grouping

Question

I have 3 collections:

Plan
Business Process
Recovery Strategy

The three of them store their Primary Key in _id and

I have another collection: planRecoveryStrategy which stores

planId
businessProcessId
recoveryStrategyId

Example documents stored in planRecoveryStrategy:

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

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

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

What is the query to write to get given the planId is '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>}]
    }]
}

from planRecoveryStrategy?

Lemix · Accepted Answer · 2018-10-03 15:32:38Z

1

Try it for grouping data:

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"
                }
            }
        }
    }
]);

With sort:

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"
                }
            }
        }
    }
]);

With including other collections:

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"
                }
            }
        }
    }
]);

edited Oct 3, 2018 at 15:32

answered Oct 3, 2018 at 13:03

Lemix

3,0002 gold badges17 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Prateek Narendra Over a year ago

How do I select the details of the record from Recovery Strategy collection and store it in strategies array ?

Prateek Narendra Over a year ago

Thank you for the solution. But the requirement changed a bit

Lemix Over a year ago

@PrateekNarendra I added example with including other collections. Replace foreignField and from attributes.

Collectives™ on Stack Overflow

MongoDB Query For Nested Grouping

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related