Is it possible to join 3 separate collections together in AWS DocumentDB with a single query (similar to the solution to the thread here: How can I perform nested “joins” (joining 3 or more collections) in a MongoDB aggregation pipeline?
I feel like it may not, but I don't think AWS comes right out and specifically says so anywhere.
The Developer Guide says:
Amazon DocumentDB supports the ability to do equality matches (for example, left outer join) but does not support uncorrelated subqueries.
That would be more helpful if I knew what an "uncorrelated subquery" was.
Also, the list of MongoDB APIs that DocumentDB supports says that $let variable operator is outright not supported. Is the $let variable operator they're referring to exactly the same as the let expression within the $lookup stage that's used in the following query to join together 3 collections?
db.customers.aggregate([
{
$lookup: {
from: "orders",
let: { customer_id: "$customer_id" },
pipeline: [
{ $match: { $expr: { $eq: ["$$customer_id", "$customer_id"] } } },
{
$lookup: {
from: "orderitems",
localField: "order_id",
foreignField: "order_id",
as: "items"
}
}
],
as: "orders"
}
}
])
Is joining 3+ collections within a single NoSQL query possible in AWS DocumentDB, and if not, what would be the recommended / most efficient way to do this?
In DocumentDB doc:
Amazon DocumentDB supports the ability to do equality matches (for example, left outer join) but does not support uncorrelated subqueries.
In MongoDB doc:
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>
}
}
uncorrelated subqueries has the following syntax:
{
$lookup:
{
from: <collection to join>,
let: { <var_1>: <expression>, …, <var_n>: <expression> },
pipeline: [ <pipeline to execute on the collection to join> ],
as: <output array field>
}
}
So the 2nd syntax is not supported in DocumentDB.