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

0

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.

asked 10 months ago413 views
1 Answer
0

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

AWS
answered 10 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions