Joining multiple collections + docDB

Question

Consider I have collection 2 collections named a, b.

collection a

{
"id":"123"
"key1":"fjf",
"key2":[{},{}]
}

collectionB

{
"key1b":"fjf",
"id":"123",
"key2b":"bvbvb"
}

Expected output:

{
"id":"123"
"key1":"fjf",
"key2":[{},{}],
"joined":[{
    "key1b":"fjf",
    "id":"123",
    "key2b":"bvbvb"
}]
}

So have to join the 2 collections based on condition a.key1 == b.key1b&&a.id == b.id. SO i have used the pipepline stage as follows:

 await a.aggregate([
   { "$match": { "x": "fdsfd" } },
  {
    "$lookup": {
      "from": "b",
    "let": { "Id": "$id" },
      "pipeline": [
       {
          "$match": {
            "$expr": {
               "$and": [
                { $eq: ["$id", "$$qId"] },
                 {
                  $eq: ["$key1b", "adsdsd"],
                 },
             ],
            },
           }
         }
       ],
       "as": "abc"
     }
   }
 ])

Its works fine in mongoDb. But $let is not supported in DocDB as per AWS doc. So how can i rewrite the query the above query without $let?

Tried the following:

• Does AWS DocumentDB Support Joining 3+ Collections In a Single Query?
• $match in $lookup result

etc. But noting helps.

Answer 1

Listed below is one alternate way of getting the results you seek in DocumentDB. It uses the aggregate pipeline and $lookup stage which is equivalent to a left join and then removes records which do which may have null values due to the left join. Please note that if "id" field is not unique this may return duplicates. Also when using aggregate pipeline, try and use $match first to reduce the records being processed in the pipeline. Do check the performance of the queries against your peak volumes to ensure optimal response.

db.tableA.aggregate([
{
    $lookup:
        {
          from: "tableB",
          localField: "id",
          foreignField: "id",
          as: "joined"
        }
},  
{ $unwind :"$joined" },
{
  $redact: { $cond: [ { $eq: [ "$key1", "$joined.key1b" ] }, "$$KEEP", "$$PRUNE" ] } 
}
])

Do share if this works for you.