MongoDB nested lookup with 3 levels
<p>I need to retrieve the entire single object hierarchy from the database as a JSON. Actually, the proposal about any other solution to achieve this result would be highly appreciated. I decided to use MongoDB with its <code>$lookup</code> support.</p>
<p>So I have three collections:</p>
<p><strong>party</strong></p>
<pre><code>{ &quot;_id&quot; : &quot;2&quot;, &quot;name&quot; : &quot;party2&quot; }
{ &quot;_id&quot; : &quot;5&quot;, &quot;name&quot; : &quot;party5&quot; }
{ &quot;_id&quot; : &quot;4&quot;, &quot;name&quot; : &quot;party4&quot; }
{ &quot;_id&quot; : &quot;1&quot;, &quot;name&quot; : &quot;party1&quot; }
{ &quot;_id&quot; : &quot;3&quot;, &quot;name&quot; : &quot;party3&quot; }    
</code></pre>
<p><strong>address</strong></p>
<pre><code>{ &quot;_id&quot; : &quot;a3&quot;, &quot;street&quot; : &quot;Address3&quot;, &quot;party_id&quot; : &quot;2&quot; }
{ &quot;_id&quot; : &quot;a6&quot;, &quot;street&quot; : &quot;Address6&quot;, &quot;party_id&quot; : &quot;5&quot; }
{ &quot;_id&quot; : &quot;a1&quot;, &quot;street&quot; : &quot;Address1&quot;, &quot;party_id&quot; : &quot;1&quot; }
{ &quot;_id&quot; : &quot;a5&quot;, &quot;street&quot; : &quot;Address5&quot;, &quot;party_id&quot; : &quot;5&quot; }
{ &quot;_id&quot; : &quot;a2&quot;, &quot;street&quot; : &quot;Address2&quot;, &quot;party_id&quot; : &quot;1&quot; }
{ &quot;_id&quot; : &quot;a4&quot;, &quot;street&quot; : &quot;Address4&quot;, &quot;party_id&quot; : &quot;3&quot; }
</code></pre>
<p><strong>addressComment</strong></p>
<pre><code>{ &quot;_id&quot; : &quot;ac2&quot;, &quot;address_id&quot; : &quot;a1&quot;, &quot;comment&quot; : &quot;Comment2&quot; }
{ &quot;_id&quot; : &quot;ac1&quot;, &quot;address_id&quot; : &quot;a1&quot;, &quot;comment&quot; : &quot;Comment1&quot; }
{ &quot;_id&quot; : &quot;ac5&quot;, &quot;address_id&quot; : &quot;a5&quot;, &quot;comment&quot; : &quot;Comment6&quot; }
{ &quot;_id&quot; : &quot;ac4&quot;, &quot;address_id&quot; : &quot;a3&quot;, &quot;comment&quot; : &quot;Comment4&quot; }
{ &quot;_id&quot; : &quot;ac3&quot;, &quot;address_id&quot; : &quot;a2&quot;, &quot;comment&quot; : &quot;Comment3&quot; }
</code></pre>
<p>I need to retrieve all parties with all corresponding addresses and address comments as part of the record. My aggregation:</p>
<pre><code>db.party.aggregate([{
    $lookup: {
        from: &quot;address&quot;,
        localField: &quot;_id&quot;,
        foreignField: &quot;party_id&quot;,
        as: &quot;address&quot;
    }
},
{
    $unwind: &quot;$address&quot;
},
{
    $lookup: {
        from: &quot;addressComment&quot;,
        localField: &quot;address._id&quot;,
        foreignField: &quot;address_id&quot;,
        as: &quot;address.addressComment&quot;
    }
}])
</code></pre>
<p>The result is pretty weird. Some records are ok. But Party with <code>_id: 4</code> is missing (there is no address for it). Also, there are two Party <code>_id: 1</code> in the result set (but with different addresses):</p>
<pre><code>{
    &quot;_id&quot;: &quot;1&quot;,
    &quot;name&quot;: &quot;party1&quot;,
    &quot;address&quot;: {
        &quot;_id&quot;: &quot;2&quot;,
        &quot;street&quot;: &quot;Address2&quot;,
        &quot;party_id&quot;: &quot;1&quot;,
        &quot;addressComment&quot;: [{
            &quot;_id&quot;: &quot;3&quot;,
            &quot;address_id&quot;: &quot;2&quot;,
            &quot;comment&quot;: &quot;Comment3&quot;
        }]
    }
}{
    &quot;_id&quot;: &quot;1&quot;,
    &quot;name&quot;: &quot;party1&quot;,
    &quot;address&quot;: {
        &quot;_id&quot;: &quot;1&quot;,
        &quot;street&quot;: &quot;Address1&quot;,
        &quot;party_id&quot;: &quot;1&quot;,
        &quot;addressComment&quot;: [{
            &quot;_id&quot;: &quot;1&quot;,
            &quot;address_id&quot;: &quot;1&quot;,
            &quot;comment&quot;: &quot;Comment1&quot;
        },
        {
            &quot;_id&quot;: &quot;2&quot;,
            &quot;address_id&quot;: &quot;1&quot;,
            &quot;comment&quot;: &quot;Comment2&quot;
        }]
    }
}{
    &quot;_id&quot;: &quot;3&quot;,
    &quot;name&quot;: &quot;party3&quot;,
    &quot;address&quot;: {
        &quot;_id&quot;: &quot;4&quot;,
        &quot;street&quot;: &quot;Address4&quot;,
        &quot;party_id&quot;: &quot;3&quot;,
        &quot;addressComment&quot;: []
    }
}{
    &quot;_id&quot;: &quot;5&quot;,
    &quot;name&quot;: &quot;party5&quot;,
    &quot;address&quot;: {
        &quot;_id&quot;: &quot;5&quot;,
        &quot;street&quot;: &quot;Address5&quot;,
        &quot;party_id&quot;: &quot;5&quot;,
        &quot;addressComment&quot;: [{
            &quot;_id&quot;: &quot;5&quot;,
            &quot;address_id&quot;: &quot;5&quot;,
            &quot;comment&quot;: &quot;Comment5&quot;
        }]
    }
}{
    &quot;_id&quot;: &quot;2&quot;,
    &quot;name&quot;: &quot;party2&quot;,
    &quot;address&quot;: {
        &quot;_id&quot;: &quot;3&quot;,
        &quot;street&quot;: &quot;Address3&quot;,
        &quot;party_id&quot;: &quot;2&quot;,
        &quot;addressComment&quot;: [{
            &quot;_id&quot;: &quot;4&quot;,
            &quot;address_id&quot;: &quot;3&quot;,
            &quot;comment&quot;: &quot;Comment4&quot;
        }]
    }
}
</code></pre>
<p>Please help me with this. I'm pretty new to MongoDB but I feel it can do what I need from it.</p>