$unwind and $group multiple times

Question

I have the following sample collection Contract:

/* 1 */
{
    "_id" : "Contract_1",
    "ContactId" : "Contact_1",
    "Specifications" : [ 
        {
            "Description" : "Descrizione1",
            "VehicleId" : "Vehicle_1",
            "Customizations" : [ 
                {
                    "Description" : "Random furniture",
                    "ContactId" : "Contact_5"
                }, 
                {
                    "Description" : "Random furniture 2",
                    "ContactId" : "Contact_3"
                }
            ]
        }, 
        {
            "Description" : "Descrizione2",
            "VehicleId" : "Vehicle_2",
            "Customizations" : [ 
                {
                    "Description" : "Random furniture 3",
                    "ContactId" : "Contact_5"
                }, 
                {
                    "Description" : "Random furniture 4",
                    "ContactId" : "Contact_3"
                }
            ]
        }
    ]
}

/* 2 */
{
    "_id" : "Contract_2",
    "ContactId" : "Contact_2",
    "Specifications" : [ 
        {
            "Description" : "Descrizione1",
            "VehicleId" : "Vehicle_1",
            "Customizations" : [ 
                {
                    "Description" : "Random furniture",
                    "ContactId" : "Contact_5"
                }, 
                {
                    "Description" : "Random furniture 2",
                    "ContactId" : "Contact_3"
                }
            ]
        }, 
        {
            "Description" : "Descrizione2",
            "VehicleId" : "Vehicle_2",
            "Customizations" : [ 
                {
                    "Description" : "Random furniture",
                    "ContactId" : "Contact_5"
                }, 
                {
                    "Description" : "Random furniture 2",
                    "ContactId" : "Contact_3"
                }
            ]
        }
    ]
}

ContactId and VehicleId needs to be retrieved by lookup from their respective collections. To do so, I did the following query:

    db.getCollection('Contract').aggregate([
    {$lookup:
        { 
        from: "Contact",
        localField: "ContactId",
        foreignField: "_id",
        as: "Contact"
        }
     },
     {$unwind: "$Contact"},
     {$unwind: "$Specifications"},
     {$lookup:
        { 
        from: "Vehicle",
        localField: "Specifications.VehicleId",
        foreignField: "_id",
        as: "Specifications.Vehicle"
        }
     },
     {$unwind: "$Specifications.Vehicle"},
     {$unwind: "$Specifications.Customizations"},
     {$lookup:
        { 
        from: "Contact",
        localField: "Specifications.Customizations.ContactId",
        foreignField: "_id",
        as: "Specifications.Customizations.Contact"
        }
     },
     {$unwind: "$Specifications.Customizations.Contact"}
])

And I get something like this:

/* 1 */
{
    "_id" : "Contract_1",
    "ContactId" : "Contact_1",
    "Specifications" : {
        "Description" : "Descrizione1",
        "VehicleId" : "Vehicle_1",
        "Vehicle" : {
            "_id" : "Vehicle_1",
            "FrameNumber" : "asdasd33",
        },
        "Customizations" : {
            "Description" : "Random furniture",
            "ContactId" : "Contact_5",
            "Contact" : {
                "_id" : "Contact_5",
                "Name" : "Nome5"
            }
        }
    },
    "Contact" : {
        "_id" : "Contact_1",
        "Name" : "Nome"
    }
}

/* 2 */
{
    "_id" : "Contract_1",
    "ContactId" : "Contact_1",
    "Specifications" : {
        "Description" : "Descrizione1",
        "VehicleId" : "Vehicle_1",
        "Vehicle" : {
            "_id" : "Vehicle_1",
            "FrameNumber" : "asdasd33",
        },
        "Customizations" : {
            "Description" : "Random furniture 2",
            "ContactId" : "Contact_3",
            "Contact" : {
                "_id" : "Contact_3",
                "Name" : "Nome3"
            }
        }
    },
    "Contact" : {
        "_id" : "Contact_1",
        "Name" : "Nome"
    }
}

/* 3 */
{
    "_id" : "Contract_1",
    "ContactId" : "Contact_1",
    "Specifications" : {
        "Description" : "Descrizione2",
        "VehicleId" : "Vehicle_2",
        "Vehicle" : {
            "_id" : "Vehicle_2",
            "FrameNumber" : "frame2",
        },
        "Customizations" : {
            "Description" : "Random furniture 3",
            "ContactId" : "Contact_5",
            "Contact" : {
                "_id" : "Contact_5",
                "Name" : "Nome5"
            }
        }
    },
    "Contact" : {
        "_id" : "Contact_1",
        "Name" : "Nome"
    }
}

/* 4 */
{
    "_id" : "Contract_1",
    "ContactId" : "Contact_1",
    "Specifications" : {
        "Description" : "Descrizione2",
        "VehicleId" : "Vehicle_2",
        "Vehicle" : {
            "_id" : "Vehicle_2",
            "FrameNumber" : "frame2",
        },
        "Customizations" : {
            "Description" : "Random furniture 4",
            "ContactId" : "Contact_3",
            "Contact" : {
                "_id" : "Contact_3",
                "Name" : "Nome3"
            }
        }
    },
    "Contact" : {
        "_id" : "Contact_1",
        "Name" : "Nome"
    }
}

/* 5 */
{
    "_id" : "Contract_2",
    "ContactId" : "Contact_2",
    "Specifications" : {
        "Description" : "Descrizione1",
        "VehicleId" : "Vehicle_1",
        "Vehicle" : {
            "_id" : "Vehicle_1",
            "FrameNumber" : "asdasd33",
        },
        "Customizations" : {
            "Description" : "Random furniture",
            "ContactId" : "Contact_5",
            "Contact" : {
                "_id" : "Contact_5",
                "Name" : "Nome5"
            }
        }
    },
    "Contact" : {
        "_id" : "Contact_2",
        "Name" : "Nome"
    }
}

/* 6 */
{
    "_id" : "Contract_2",
    "ContactId" : "Contact_2",
    "Specifications" : {
        "Description" : "Descrizione1",
        "VehicleId" : "Vehicle_1",
        "Vehicle" : {
            "_id" : "Vehicle_1",
            "FrameNumber" : "asdasd33",
        },
        "Customizations" : {
            "Description" : "Random furniture 2",
            "ContactId" : "Contact_3",
            "Contact" : {
                "_id" : "Contact_3",
                "Name" : "Nome3"
            }
        }
    },
    "Contact" : {
        "_id" : "Contact_2",
        "Name" : "Nome"
    }
}

/* 7 */
{
    "_id" : "Contract_2",
    "ContactId" : "Contact_2",
    "Specifications" : {
        "Description" : "Descrizione2",
        "VehicleId" : "Vehicle_2",
        "Vehicle" : {
            "_id" : "Vehicle_2",
            "FrameNumber" : "frame2",
        },
        "Customizations" : {
            "Description" : "Random furniture",
            "ContactId" : "Contact_5",
            "Contact" : {
                "_id" : "Contact_5",
                "Name" : "Nome5"
            }
        }
    },
    "Contact" : {
        "_id" : "Contact_2",
        "Name" : "Nome"
    }
}

/* 8 */
{
    "_id" : "Contract_2",
    "ContactId" : "Contact_2",
    "Specifications" : {
        "Description" : "Descrizione2",
        "VehicleId" : "Vehicle_2",
        "Vehicle" : {
            "_id" : "Vehicle_2",
            "FrameNumber" : "frame2",
        },
        "Customizations" : {
            "Description" : "Random furniture 2",
            "ContactId" : "Contact_3",
            "Contact" : {
                "_id" : "Contact_3",
                "Name" : "Nome3"
            }
        }
    },
    "Contact" : {
        "_id" : "Contact_2",
        "Name" : "Nome"
    }
}

How can I group everything back to get 2 Contract as the starting point, but with all the informations that I get from the lookups? I'd like to group by the Contract _id field but the 2 nested arrays don't really help to do so.

Edit: ExpectedResult:

/* 1 */
{
    "_id" : "Contract_1",
    "ContactId" : "Contact_1",
    "Contact" : {
        "_id" : "Contact_1",
        "Name" : "Nome"
    },
    "Specifications" : [ 
        {
            "Description" : "Descrizione1",
            "VehicleId" : "Vehicle_1",
            "Vehicle" : {
                "_id" : "Vehicle_1",
                "FrameNumber" : "asdasd33"
            },
            "Customizations" : [ 
                {
                    "Description" : "Random furniture",
                    "ContactId" : "Contact_5",
                    "Contact" : {
                        "_id" : "Contact_5",
                        "Name" : "Nome5"
                    }
                }, 
                {
                    "Description" : "Random furniture 2",
                    "ContactId" : "Contact_3",
                    "Contact" : {
                        "_id" : "Contact_3",
                        "Name" : "Nome3"
                    }
                }
            ]
        }, 
        {
            "Description" : "Descrizione2",
            "VehicleId" : "Vehicle_2",
            "Vehicle" : {
                "_id" : "Vehicle_2",
                "FrameNumber" : "frame2"
            },
            "Customizations" : [ 
                {
                    "Description" : "Random furniture 3",
                    "ContactId" : "Contact_5",
                    "Contact" : {
                        "_id" : "Contact_5",
                        "Name" : "Nome5"
                    }
                }, 
                {
                    "Description" : "Random furniture 4",
                    "ContactId" : "Contact_3",
                    "Contact" : {
                        "_id" : "Contact_3",
                        "Name" : "Nome3"
                    }
                }
            ]
        }
    ]
}

/* 2 */
{
    "_id" : "Contract_2",
    "ContactId" : "Contact_2",
    "Contact" : {
        "_id" : "Contact_2",
        "Name" : "Nome2"
    }
    "Specifications" : [ 
        {
            "Description" : "Descrizione1",
            "VehicleId" : "Vehicle_1",
            "Vehicle" : {
                "_id" : "Vehicle_1",
                "FrameNumber" : "asdasd33"
            },
            "Customizations" : [ 
                {
                    "Description" : "Random furniture",
                    "ContactId" : "Contact_5",
                    "Contact" : {
                        "_id" : "Contact_5",
                        "Name" : "Nome5"
                    }
                }, 
                {
                    "Description" : "Random furniture 2",
                    "ContactId" : "Contact_3",
                    "Contact" : {
                        "_id" : "Contact_3",
                        "Name" : "Nome3"
                    }
                }
            ]
        }, 
        {
            "Description" : "Descrizione2",
            "VehicleId" : "Vehicle_2",
            "Vehicle" : {
                "_id" : "Vehicle_1",
                "FrameNumber" : "frame2"
            },
            "Customizations" : [ 
                {
                    "Description" : "Random furniture",
                    "ContactId" : "Contact_5",
                    "Contact" : {
                        "_id" : "Contact_5",
                        "Name" : "Nome5"
                    }
                }, 
                {
                    "Description" : "Random furniture 2",
                    "ContactId" : "Contact_3",
                    "Contact" : {
                        "_id" : "Contact_3",
                        "Name" : "Nome3"
                    }
                }
            ]
        }
    ]
}

@AshwanthMadhav i fixed a lookup, and added the expected result. It's the starting document, with all the info retrieved from the lookups — Davide Quaglio, May 15 '19 at 09:15
I need the contact collection documents. Its just name and id ? — Ashwanth Madhav, May 15 '19 at 09:27
Yeah, also Vehicle, it's just to be easier. The real problem could be simplified even more. I need to rewind the `{$unwind: "$Specifications"}, {$unwind: "$Specifications.Customizations"}` part. You can imagine an aggregation of the former object, with just these 2 instructions, then get back to the former object. The lookups are not really important — Davide Quaglio, May 15 '19 at 09:42

Ashh · Accepted Answer · 2019-05-15T10:14:25.453

2

You can use below aggregation.

db.getCollection("Contract").aggregate([
  { "$lookup": {
    "from": "Contact",
    "localField": "ContactId",
    "foreignField": "_id",
    "as": "Contact"
  }},
  { "$unwind": "$Contact" },
  { "$unwind": "$Specifications" },
  { "$lookup": {
    "from": "Vehicle",
    "localField": "Specifications.VehicleId",
    "foreignField": "_id",
    "as": "Specifications.Vehicle"
  }},
  { "$unwind": "$Specifications.Vehicle" },
  { "$unwind": "$Specifications.Customizations" },
  { "$lookup": {
    "from": "Contact",
    "localField": "Specifications.Customizations.ContactId",
    "foreignField": "_id",
    "as": "Specifications.Customizations.Contact"
  }},
  { "$unwind": "$Specifications.Customizations.Contact" },
  { "$group": {
    "_id": {
      "_id"; "$_id",
      "Description": "$Specifications.Description"
    },
    "ContactId": { "$first": "$ContactId" },
    "Contact": { "$first": "$Contact" },
    "Specifications": {
      "$push": "$Specifications.Customizations"
    }
  }},
  { "$group": {
    "_id": "$_id._id",
    "ContactId": { "$first": "$ContactId" },
    "Contact": { "$first": "$Contact" },
    "Specifications": {
      "$push": {
        "Description": "$_id.Description"
        "Customizations": "$Specifications"
      }
    }
  }}
])

edited May 15 '19 at 10:14

answered May 15 '19 at 10:02

Ashh

44,693
14
105
132

1

Hi, this is really close to what I need, in the Customizations array, there is an object called Customizations itself, how is it possible to remove it? `"Customizations" : [ { "Customizations" : { // remove this one "Description" : "Random furniture 3",` – Davide Quaglio May 15 '19 at 10:11
Updated my answer. That was my fault – Ashh May 15 '19 at 10:14
Hi, I just discovered that when I have Specifications as an empty array, when grouping I get an array with an element inside, empty. I'm going to create a new question for this problem. Is it a problem if i tag you? – Davide Quaglio May 26 '19 at 09:59
No you can proceed – Ashh May 26 '19 at 10:00
Hi, here is the new question, I used the same structure as this one [link](https://stackoverflow.com/questions/56312636/mongodb-group-and-push-with-empty-arrays) – Davide Quaglio May 26 '19 at 10:20

score 0 · Answer 2 · answered May 15 '19 at 10:34

db.getCollection('Contract').aggregate([  
{  
  $lookup:{  
     from:"contact",
     localField:"ContactId",
     foreignField:"_id",
     as:"contact"
  }
},
{  
  $unwind:{  
     path:"$contact",
     preserveNullAndEmptyArrays:true
  }
},
{  
  $unwind:{  
     path:"$Specifications",
     preserveNullAndEmptyArrays:true
  }
},
{  
  $unwind:{  
     path:"$Specifications.Customizations",
     preserveNullAndEmptyArrays:true
  }
},
{  
  $lookup:{  
     from:"contact",
     localField:"Specifications.Customizations.ContactId",
     foreignField:"_id",
     as:"details"
  }
},
{  
  $unwind:{  
     path:"$details",
     preserveNullAndEmptyArrays:true
  }
},
{  
  $project:{  
     ContactId:1,
     Specifications:1,
     details:1,
     contact:1
  }
},
{  
  $addFields:{  
     "Specifications.Customizations.Contact":"$details",

  }
},
{  
  $group:{  
     _id:{  
        _id:"$_id",
        ContactId:"$ContactId",
        spec:"$Specifications.Description",
        VehicleId:"$Specifications.VehicleId"
     },
     Customizations:{  
        $addToSet:"$Specifications.Customizations"
     },
     contact:{  
        $first:"$contact"
     }
  },

},
{  
  $project:{  
     spec:{  
        Description:"$_id.spec",
        VehicleId:"$_id.VehicleId",
        Customizations:"$Customizations"
     },
     contact:1
  },

},
{  
  $group:{  
     _id:"$_id._id",
     ContactId:{  
        $first:"$_id.ContactId"
     },
     contact:{  
        $first:"$contact"
     },
     Specifications:{  
        $addToSet:"$spec"
     }
  }
}
])

$unwind and $group multiple times

2 Answers2