How to Query from Multiple MongoDB Databases Using MongoDB Atlas Data Federation

1
// MongoDB Playground
2
3
// Select the database to use. VirtualDatabase0 is the default name for a MongoDB Atlas Data Federation database. If you renamed your database, be sure to put in your virtual database name here.
4
use('VirtualDatabase0');
5
6
// We are connecting to `VirtualCollection0` since this is the default collection that MongoDB Atlas Data Federation calls your collection. If you renamed it, be sure to put in your virtual collection name here.
7
db.VirtualCollection0.aggregate([
8
9
  // In the first stage of our aggregation pipeline, we extract and normalize the dataset to only extract zip code data from our dataset.
10
  {
11
    '$project': {
12
      'restaurant_zipcode': '$address.zipcode',
13
      'theater_zipcode': '$location.address.zipcode',
14
      'zipcode': {
15
        '$ifNull': [
16
          '$address.zipcode', '$location.address.zipcode'
17
        ]
18
      }
19
    }
20
  },
21
22
  // In the second stage of our aggregation, we group the data based on the zip code it resides in. We also push each unique restaurant and theater into an array, so we can get a count of the number of each in the next stage.
23
  // We are calculating the `total` number of theaters and restaurants by using the aggregator function on $group. This sums all the documents that share a common zip code.
24
  {
25
    '$group': {
26
      '_id': '$zipcode',
27
      'total': {
28
        '$sum': 1
29
      },
30
      'theaters': {
31
        '$push': '$theater_zipcode'
32
      },
33
      'restaurants': {
34
        '$push': '$restaurant_zipcode'
35
      }
36
    }
37
  },
38
39
  // In the third stage, we get the size or length of the `theaters` and `restaurants` array from the previous stage. This gives us our totals for each category.
40
  {
41
    '$project': {
42
      'zipcode': '$_id',
43
      'total': '$total',
44
      'total_theaters': {
45
        '$size': '$theaters'
46
      },
47
      'total_restaurants': {
48
        '$size': '$restaurants'
49
      }
50
    }
51
  },
52
53
  // In our final stage, we sort our data in descending order so that the zip codes with the most number of restaurants and theaters are listed at the top.
54
  {
55
    '$sort': {
56
      'total': -1
57
    }
58
  }
59
])

1
[
2
  {
3
    "_id": "10003",
4
    "zipcode": "10003",
5
    "total": 688,
6
    "total_theaters": 2,
7
    "total_restaurants": 686
8
  },
9
  {
10
    "_id": "10019",
11
    "zipcode": "10019",
12
    "total": 676,
13
    "total_theaters": 1,
14
    "total_restaurants": 675
15
  },
16
  {
17
    "_id": "10036",
18
    "zipcode": "10036",
19
    "total": 611,
20
    "total_theaters": 0,
21
    "total_restaurants": 611
22
  },
23
  {
24
    "_id": "10012",
25
    "zipcode": "10012",
26
    "total": 408,
27
    "total_theaters": 1,
28
    "total_restaurants": 407
29
  },
30
  {
31
    "_id": "11354",
32
    "zipcode": "11354",
33
    "total": 379,
34
    "total_theaters": 1,
35
    "total_restaurants": 378
36
  },
37
  {
38
    "_id": "10017",
39
    "zipcode": "10017",
40
    "total": 378,
41
    "total_theaters": 1,
42
    "total_restaurants": 377
43
  }
44
 ]