How to do left outer join and count in AWS DocumentDB?

Question

I am trying to get count of tenant for the owner,

owner_collection:

[ 
{_id: ObjectId("123dddfffaaa7744"), owner_name:"Sam"},
{_id: ObjectId("243dddfffaaa7755"), owner_name:"Ray"}
]

tenant_collection:

[
{_id: ObjectId("2223dddfffaaa12233"), tenant_name:"tet", owner_id: ObjectId("123dddfffaaa7744")},
{_id: ObjectId("3343dddfffaaa12234"), tenant_name:"sothy", owner_id: ObjectId("123dddfffaaa7744")},
{_id: ObjectId("6583dddfffaaa1876"), tenant_name:"gill", owner_id: ObjectId("243dddfffaaa7755")},
{_id: ObjectId("2223dddfffaaa12233"), tenant_name:"tony", owner_id: ObjectId("123dddfffaaa7744")}
]

In mongodb as it is like this,

db.getCollection("owner_collection").aggregate([
    {
         $lookup:{from:"tenant_collection",localField:"_id",foreignField:"owner_id", as:"tenantCount"}
    },{
        $addFields:{
                 tenantCount:{$size:"$tenantCount"}
        }
   }
]);

but when i am executing this in aws DocumentDB (mongodb compatibile), it throws error

invalid $lookup namespace

Is there any way to achieve in DocumentDB ?

score 0 · Answer 1 · answered Oct 31 '22 at 12:04

The error suggests that the collection you are joining from (tenant_collection) doesn't exist, that is the standard error message of DocumentDB for such a case. This is a different behavior than MongoDB, which doesn't complain when joining with non-existing collection and returns the results from the left hand side collection. To double check on this, just create an empty collection in DocumentDB and run the $lookup again ( db.createCollection("tenant_collection") )

How to do left outer join and count in AWS DocumentDB?

1 Answers1