0

Let us say, there are 2 databases db1 and db2. db1 has 2 collections - collection1 and collection2. db2 has collection3.

For the first step, I will join collection1 and collection2 using $lookup. The output from this aggregation stage has to be used to join collection3 from db2.

Could you please help me if we can do this.

MQL does not have joining collections from multiple databases and I understand Atlas DataLake has this but Im looking at something from Python or JavaScript perspective.

db1

collection1

/* 1 */
{
    "_id" : 1.0,
    "item" : "almonds",
    "price" : 12.0,
    "quantity" : 2.0
}

/* 2 */
{
    "_id" : 2.0,
    "item" : "pecans",
    "price" : 20.0,
    "quantity" : 1.0
}

collection2

/* 1 */
{
    "_id" : 11.0,
    "sku" : "almonds",
    "description" : "product 1",
    "instock" : 120.0
}

/* 2 */
{
    "_id" : 12.0,
    "sku" : "bread",
    "description" : "product 2",
    "instock" : 80.0
}

/* 3 */
{
    "_id" : 13.0,
    "sku" : "cashews",
    "description" : "product 3",
    "instock" : 60.0
}

/* 4 */
{
    "_id" : 14.0,
    "sku" : "pecans",
    "description" : "product 4",
    "instock" : 70.0
}

/* 5 */
{
    "_id" : 15.0,
    "sku" : null,
    "description" : "Incomplete"
}

$lookup collection1 and collection2 with localField - item and foreignField - sku

db2

collection3

/* 1 */
{
    "_id" : 1.0,
    "sku" : "almonds",
    "description" : "product 1",
    "instock" : 120.0
}

/* 2 */
{
    "_id" : 2.0,
    "sku" : "bread",
    "description" : "product 2",
    "instock" : 80.0
}

/* 3 */
{
    "_id" : 3.0,
    "sku" : "cashews",
    "description" : "product 3",
    "instock" : 60.0
}

/* 4 */
{
    "_id" : 4.0,
    "sku" : "pecans",
    "description" : "product 4",
    "instock" : 70.0
}

/* 5 */
{
    "_id" : 5.0,
    "sku" : null,
    "description" : "Incomplete"
}

/* 6 */
{
    "_id" : 6.0
}

/* 7 */
{
    "_id" : 7.0,
    "sku" : "almonds",
    "description" : "product 1",
    "instock" : 120.0
}

sku as the field to join

Himanshu Poddar
  • 7,112
  • 10
  • 47
  • 93
S.E
  • 1
  • Does this answer your question? [MongoDB: Combine data from multiple collections into one..how?](https://stackoverflow.com/questions/5681851/mongodb-combine-data-from-multiple-collections-into-one-how) – Alireza Abdi Jun 23 '22 at 15:50
  • @Abdi - No. I am looking for ways to join collections from 2 different databases. without using Atlas Data Lake. – S.E Jul 01 '22 at 09:30
  • maybe join two collections form json fromat? https://stackoverflow.com/questions/22698244/how-to-merge-two-json-string-in-python – Alireza Abdi Jul 02 '22 at 13:06

0 Answers0