DocumentDB | Mongoerror: aggregation stage not supported: '$lookup on multiple join conditions and uncorrelated subquery

user_8503505 0
Viewed 1672

I am a newbie to DocumentDB. Trying to join two collections and perform filters like query, sort and projection.

I have two collections and need to perform filters on both the collections and the result should be a embeded list (Which should contain both items and subitems).

First collection will hold the id's of the second collection in a Subitems field.

Here is the first collection (Items):

{
    "_id": 897979789,
    "Country": "India",
    "State": "XXX",
    "Subitems": [ ObjectId("44497979789"), ObjectId("333897979789") ]
},
{
    "_id": 987979798,
    "Country": "Australia",
    "State": "YYY",
    "Subitems": [ ObjectId("97979789444"), ObjectId("897979789333") ]
}

Here is the second collection:

{
"_id": 44497979789,
"EmployeeName": "Krishna",
"Occupation": "Engineer",
"ProjectsHandled": [
    {
        "ProjectName": "Project1"
    }
]
},
{
    "_id": 333897979789,
    "EmployeeName": "krish",
    "Occupation": "CTO",
    "ProjectsHandled": [
        {
            "ProjectName": "Project1"
        }
    ]
},
{
    "_id": 97979789444,
    "EmployeeName": "name",
    "Occupation": "CEO",
    "ProjectsHandled": [
        {
            "ProjectName": "Project2"
        }
    ]
},
{
    "_id": 897979789333,
    "EmployeeName": "name1",
    "Occupation": "manager",
    "ProjectsHandled": [
        {
            "ProjectName": "Project3"
        }
    ]
}

Here is my query:

let subItemPipeline: any[] = [{ $match: config.subItemsQuery }];
        if(Object.keys(config.subItemsSort || {}).length)
            subItemPipeline.push({$sort: config.subItemsSort});
        if(Object.keys(config.subItemsProjection || {}).length)
            subItemPipeline.push({$project: config.subItemsProjection});
            
        let query: any[] = [
            { 
                $match: config.itemsQuery
            },
            {
                $lookup: {
                    from: "Subitems",
                    pipeline: subItemPipeline,
                    as: "Subitems"
                }
            }
        ];

        if(Object.keys(config.overallQuery || {}).length) query.push({$match: config.overallQuery});
        if(Object.keys(config.itemsSort || {}).length) query.push({$sort: config.itemsSort});
        if(Object.keys(config.itemsProjection || {}).length) query.push({$project: config.itemsProjection});

        const items = await collection.aggregate(query).toArray();

It is working fine in MongoDB, but am facing the following error in DocumentDB:

Mongoerror: aggregation stage not supported: '$lookup on multiple join conditions and uncorrelated subquery

Any assistance will be appreciated

aggregation-framework mongodb-query mongodb
1 Answers

From the DocumentDB docs:

Amazon DocumentDB supports the ability to do equality matches (for example, left outer join) but does not support uncorrelated subqueries.

The Mongo docs specifically mention this:

equality match has the following syntax:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

This is the $lookup syntax supported by DocumentDB, the syntax you're trying to use is the "new" $lookup syntax added in version 3.6 which is what they call "Uncorrelated Sub-queries", unfortunately it is not supported by DocumentDB as of right now.

The easiest "quick" solution would be to add a new field to all Subitems documents with a default value. this will allow you to use the equality match lookup.

--- EDIT --- I'm suggesting you add to every single document in Subitems a new field.

Subitems.updateMany(
    {
        
    },
    {
        $set: {
            n: 1
        }
    }
)

And the same to your employee's collection:

employee.updateMany(
    {
        
    },
    {
        $set: {
            n: 1
        }
    }
)

Now you can use the equality lookup syntax:

{
    $lookup: {
        from: 'Subitems',
        localField: 'n',
        foreignField: 'n',
        as: 'Subitems',
    },
}
... continue to filter sub items.

Note that this is a toy example, it will literally lookup the entire sub item collection before you get to filter it.

If you really want a good scaleable solution you have to just split it into 2 different calls.

user_107703700
Related