Amazon DocumentDB does not support uncorrelated subqueries for the $lookup queries

Question

I have to migrate from MongoDB to Amazon DocumentDB but Amazon DocumentDB, unfortunately, does not support the 'uncorrelated subquery'. So how to change the $lookup query structure based on Amazon DocumentDB? Can you help me?

Example query as worked in MongoDB:
**Input: **
order collections:
[
  { _id: 1, customerId: 100, total: 50, orgID: '123'},
  { _id: 2, customerId: 200, total: 75, orgID: '124' },
  { _id: 3, customerId: 100, total: 100, orgID: '125' },
  { _id: 4, customerId: 300, total: 150, orgID: '126'}
]

customers collections:
[
  { _uid: 100, name: "Alice", orgID: '123', from: '2'  },
  { _uid: 200, name: "Bob", orgID: '124', from: '3' },
  { _uid: 300, name: "Charlie", orgID: '125' , from: '3' }
]

**query:**
db.order.aggregate([
  {
    $lookup: {
      from: "customers",
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: ["$orgID", '123'],
                },
                {
                  $gte: ["$from", '1'],
                },
                {
                  $lte: ["$from", '5'],
                },
              ],
            },
          },
        },
      ],
      as: "customers",
    },
  },
])

**For mongoDB it's run fine **

**Result for the above same query as Amazon DocumentDB come error as below:**
MongoServerError: Aggregation stage not supported: '$lookup on multiple join conditions and uncorrelated subquery'

I need a solution for the above scenario.

Answer

As of now , DocDB only support below approach of $lookup with appregate
db.order.aggregate([ { $lookup: { from: "customers", as: "customers", localField: "customerId", foreignField: "_id"} }])

Usage multiple join conditions and uncorrelated subquery' with $lookup is not supported and is documented:
[+] https://docs.aws.amazon.com/documentdb/latest/developerguide/functional-differences.html#functional-differences.with-mongodb

At current point only below level of joins are supported where you map the local filed to foreign filed:

> db.order.aggregate([ { $lookup: { from: "customers", as: "customers", localField: "customerId", foreignField: "_id"} }])
{ "_id" : 1, "customerId" : 100, "total" : 50, "orgID" : "123", "customers" : [ { "_id" : 100, "name" : "Alice", "orgID" : "123", "from" : "2" } ] }
{ "_id" : 2, "customerId" : 200, "total" : 75, "orgID" : "124", "customers" : [ { "_id" : 200, "name" : "Bob", "orgID" : "124", "from" : "3" } ] }
{ "_id" : 3, "customerId" : 100, "total" : 100, "orgID" : "125", "customers" : [ { "_id" : 100, "name" : "Alice", "orgID" : "123", "from" : "2" } ] }
{ "_id" : 4, "customerId" : 300, "total" : 150, "orgID" : "126", "customers" : [ { "_id" : 300, "name" : "Charlie", "orgID" : "125", "from" : "3" } ] }

As we are working backwards from customer needs, these operators may be supported in the future versions of the service.

Kindly Follow us on the below for the latest updates.

[-] AWS RDS Discussion Forums - https://forums.aws.amazon.com/forum.jspa?forumID=60    
[-] What's New with AWS - https://aws.amazon.com/new/#database-services

Amazon DocumentDB does not support uncorrelated subqueries for the $lookup queries

Relevanter Inhalt