Joining two collections on multiple columns in AWS Document DB

Asked Nov 15 '22 at 08:26

Active Nov 15 '22 at 08:26

Viewed 83 times

I am new to AWS DocumentDB and trying to execute a simple query that would find entries from two collections where 3 fields are the same.

SQL version:

Select * from A a join B b on A.id = B.id where a.ax = b.bx and a.ay = b.by and a.az = b.bz

I found that mongo db can get it using the following command that uses pipeline:

{ "$lookup": { "from": "A", "pipeline": [ { "$match": { "$expr": { "$eq": [ "$ax", "$bx" ] } } } ], "as": "result" }

However, the same query is not supported in document Db. While trying to run a similar command on MongoDB Compass (over document db cluster), I get a feature not supported error and an Uncorrelated query not supported error.

Am I doing something wrong here. What is the correct way of joing 2 collections on multiple column in AWS document db.

asked Nov 15 '22 at 08:26

Saurabh Tiwari

4,632
9
42
82

AWS documentDB supports MongoDB 4.0, the syntax you are trying to use, I think, came after 4.0. – Charchit Kapoor Nov 15 '22 at 09:33
What would be the way to fetch this data in AWS DDB then? Any ideas ? – Saurabh Tiwari Nov 16 '22 at 05:47
Depends on what are you trying to achieve. But looking at your SQL query, I think you are using lookup, in the wrong way. Ideally where clause in mysql, is $match in mongo – Charchit Kapoor Nov 16 '22 at 06:43

Joining two collections on multiple columns in AWS Document DB

0 Answers0