How to use $lookup from DB to Data Federation?

[Tom_Keidar1 (Tom Keidar)]

I’d like to create a view from one of my DBs which use $lookup to join data from a Data Federation, I tried using the following tutorial and this example:

$lookup — MongoDB Atlas

{
      $lookup: {
            from: { db: "DataFederationDbName", coll: "CollectionNameInDataFederation" },
            localField: "address",
            foreignField: "address",
            as: "label"
          }
        }

I got the following error:
MongoServerError: $lookup with syntax {from: {db:<>, coll:<>}, ...} is not supported for db: DataFederationDbName and coll: CollectionNameInDataFederation

I’ve tried creating the view with Mongo Compass and via js package MongoClient, both displayed similar errors.
What am I doing wrong?

Thanks!

[Benjamin_Flast]

Hey Tom,

When using Data Federation you are creating virtual databases and virtual collections that can source data from any of the supported sources. You can then do a $lookup using those virtual collections and databases, and it’s true you can do a cross DB lookup with the Virtual Dbs and Virtual Collections.

Based on the error message you’re getting I’m guessing that you’re actually connecting to the cluster itself (not the Federated Database Instance and Virtual DBs and Collections), and the cluster itself does not support cross DB $lookup. Can you double check that you’re using the connection string provided when you click connect on the Federated Database Instance?

Best,
Ben

[Tom_Keidar1 (Tom Keidar)]

Thanks @Benjamin_Flast, indeed I was using the cluster DB.
I’ll try again and will update you here.

Does it mean I will have to add to the data federation each and every collection I’d like to join (cross DBs)?

[Benjamin_Flast]

Yes exactly, you would need to add each.

But if you just want to have every Database and Collection from your cluster represented in your Federated Database instance we actually have a short hand for that, you just put in your storage configuration (via the UI editor) something like this:

"databases" : [
  {
    "name" : "*",
    "collections" : [
      {
        "name" : "*",
        "dataSources" : [
          {
            "storeName" : "<atlas-store-name>",
          }
        ]
      }
    ]
  }
]