4

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?

Greg Thomas
  • 397
  • 3
  • 13
  • See (Join Conditions and Uncorrelated Sub-queries)[Join Conditions and Uncorrelated Sub-queries] introduced in MongoDB 3.6. I believe DocumentDB has feature parity with MongoDB 3.4 – Joe Feb 22 '21 at 04:57
  • If you meant to post a link, it's not working. – Greg Thomas Feb 22 '21 at 14:14
  • Yeah, messed that up. I was trying to link to https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/index.html#join-conditions-and-uncorrelated-sub-queries – Joe Feb 22 '21 at 16:33
  • 1
    I still don't understand what an uncorrelated subquery is. That page doesn't really break down what that term means. Can you elaborate on what that term means? – Greg Thomas Feb 22 '21 at 17:32

1 Answers1

2

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.

kelviN
  • 975
  • 6
  • 16
  • Is there an alternative way to execute pipeline on joined collection with documentDB? It's annoying the syntax isn't supported. – Jyhonn Jul 27 '22 at 04:00