Acessing values from another MongoDB collection using ObjectID as a foreign key-CodePudding

I have two collections in a MongoDB database.

Schema of collection A is {_id, lat, lon}

Scheme of collection B is {_id, lat, lon, collectionA_id}

I want to iterate through collection B and get the lat and lon values from collection B as well as the corresponding lat lon values from collection A for that document using collectionA_id. I want to perform statistical operations on the result so the output format can be a JSON object.

What will be the query for this operation?

CodePudding user response：

This is the query I came up with

db = db.getSiblingDB("db");
db.getCollection("collectionB").aggregate(
  [
    {
      "$lookup" : {
        "from" : "collectionA",
        "localField" : "collectionA_id",
        "foreignField" : "_id",
        "as" : "join_object"
      }
    },
    {
      "$project" : {
        "latitude" : 1,
        "longitude" : 1,
        "join_object.GPSLatitude" : 1,
        "join_object.GPSLongitude" : 1
      }
    },
    {
      "$out" : {
        "db" : "db",
        "coll" : "new_collection"
      }
    }
  ],
  {
    "allowDiskUse" : false
  }
);

And then I just exported the new collection in csv format.