Como usar a fase do pipeline de agregação Union All no MongoDB 4.4

1
db.collection.aggregate([
2
  { $unionWith: "<anotherCollection>" }
3
])

1
db.collection.aggregate([
2
  { $unionWith: { coll: "<anotherCollection>", pipeline: [ <stage1>, etc. ] } }
3
])

1
SELECT column1, expression1, column2
2
FROM table1
3
UNION ALL
4
SELECT column1, expression1, column2
5
FROM table2
6
WHERE [conditions]

1
use('union-walkthrough');

1
// Insert a few documents into the lonely_planets collection.
2
db.lonely_planets.insertMany([
3
    {
4
        "name": "Endor",
5
        "rotation_period": "18",
6
        "orbital_period": "402",
7
        "diameter": "4900",
8
        "climate": "temperate",
9
        "gravity": "0.85 standard",
10
        "terrain": "forests, mountains, lakes",
11
        "surface_water": "8",
12
        "population": "30000000",
13
        "residents": [
14
            "http://swapi.dev/api/people/30/"
15
        ],
16
        "films": [
17
            "http://swapi.dev/api/films/3/"
18
        ],
19
        "created": "2014-12-10T11:50:29.349000Z",
20
        "edited": "2014-12-20T20:58:18.429000Z",
21
        "url": "http://swapi.dev/api/planets/7/"
22
    },
23
    {
24
        "name": "Kamino",
25
        "rotation_period": "27",
26
        "orbital_period": "463",
27
        "diameter": "19720",
28
        "climate": "temperate",
29
        "gravity": "1 standard",
30
        "terrain": "ocean",
31
        "surface_water": "100",
32
        "population": "1000000000",
33
        "residents": [
34
            "http://swapi.dev/api/people/22/",
35
            "http://swapi.dev/api/people/72/",
36
            "http://swapi.dev/api/people/73/"
37
        ],
38
        "films": [
39
            "http://swapi.dev/api/films/5/"
40
        ],
41
        "created": "2014-12-10T12:45:06.577000Z",
42
        "edited": "2014-12-20T20:58:18.434000Z",
43
        "url": "http://swapi.dev/api/planets/10/"
44
    },
45
    {
46
        "name": "Yavin IV",
47
        "rotation_period": "24",
48
        "orbital_period": "4818",
49
        "diameter": "10200",
50
        "climate": "temperate, tropical",
51
        "gravity": "1 standard",
52
        "terrain": "jungle, rainforests",
53
        "surface_water": "8",
54
        "population": "1000",
55
        "residents": [],
56
        "films": [
57
            "http://swapi.dev/api/films/1/"
58
        ],
59
        "created": "2014-12-10T11:37:19.144000Z",
60
        "edited": "2014-12-20T20:58:18.421000Z",
61
        "url": "http://swapi.dev/api/planets/3/"
62
    },
63
    {
64
        "name": "Hoth",
65
        "rotation_period": "23",
66
        "orbital_period": "549",
67
        "diameter": "7200",
68
        "climate": "frozen",
69
        "gravity": "1.1 standard",
70
        "terrain": "tundra, ice caves, mountain ranges",
71
        "surface_water": "100",
72
        "population": "unknown",
73
        "residents": [],
74
        "films": [
75
            "http://swapi.dev/api/films/2/"
76
        ],
77
        "created": "2014-12-10T11:39:13.934000Z",
78
        "edited": "2014-12-20T20:58:18.423000Z",
79
        "url": "http://swapi.dev/api/planets/4/"
80
    },
81
    {
82
        "name": "Bespin",
83
        "rotation_period": "12",
84
        "orbital_period": "5110",
85
        "diameter": "118000",
86
        "climate": "temperate",
87
        "gravity": "1.5 (surface), 1 standard (Cloud City)",
88
        "terrain": "gas giant",
89
        "surface_water": "0",
90
        "population": "6000000",
91
        "residents": [
92
            "http://swapi.dev/api/people/26/"
93
        ],
94
        "films": [
95
            "http://swapi.dev/api/films/2/"
96
        ],
97
        "created": "2014-12-10T11:43:55.240000Z",
98
        "edited": "2014-12-20T20:58:18.427000Z",
99
        "url": "http://swapi.dev/api/planets/6/"
100
    }
101
]);
102
103
// Insert a few documents into the popular_planets collection.
104
db.popular_planets.insertMany([
105
    {
106
        "name": "Tatooine",
107
        "rotation_period": "23",
108
        "orbital_period": "304",
109
        "diameter": "10465",
110
        "climate": "arid",
111
        "gravity": "1 standard",
112
        "terrain": "desert",
113
        "surface_water": "1",
114
        "population": "200000",
115
        "residents": [
116
            "http://swapi.dev/api/people/1/",
117
            "http://swapi.dev/api/people/2/",
118
            "http://swapi.dev/api/people/4/",
119
            "http://swapi.dev/api/people/6/",
120
            "http://swapi.dev/api/people/7/",
121
            "http://swapi.dev/api/people/8/",
122
            "http://swapi.dev/api/people/9/",
123
            "http://swapi.dev/api/people/11/",
124
            "http://swapi.dev/api/people/43/",
125
            "http://swapi.dev/api/people/62/"
126
        ],
127
        "films": [
128
            "http://swapi.dev/api/films/1/",
129
            "http://swapi.dev/api/films/3/",
130
            "http://swapi.dev/api/films/4/",
131
            "http://swapi.dev/api/films/5/",
132
            "http://swapi.dev/api/films/6/"
133
        ],
134
        "created": "2014-12-09T13:50:49.641000Z",
135
        "edited": "2014-12-20T20:58:18.411000Z",
136
        "url": "http://swapi.dev/api/planets/1/"
137
    },
138
    {
139
        "name": "Alderaan",
140
        "rotation_period": "24",
141
        "orbital_period": "364",
142
        "diameter": "12500",
143
        "climate": "temperate",
144
        "gravity": "1 standard",
145
        "terrain": "grasslands, mountains",
146
        "surface_water": "40",
147
        "population": "2000000000",
148
        "residents": [
149
            "http://swapi.dev/api/people/5/",
150
            "http://swapi.dev/api/people/68/",
151
            "http://swapi.dev/api/people/81/"
152
        ],
153
        "films": [
154
            "http://swapi.dev/api/films/1/",
155
            "http://swapi.dev/api/films/6/"
156
        ],
157
        "created": "2014-12-10T11:35:48.479000Z",
158
        "edited": "2014-12-20T20:58:18.420000Z",
159
        "url": "http://swapi.dev/api/planets/2/"
160
    },
161
    {
162
        "name": "Naboo",
163
        "rotation_period": "26",
164
        "orbital_period": "312",
165
        "diameter": "12120",
166
        "climate": "temperate",
167
        "gravity": "1 standard",
168
        "terrain": "grassy hills, swamps, forests, mountains",
169
        "surface_water": "12",
170
        "population": "4500000000",
171
        "residents": [
172
            "http://swapi.dev/api/people/3/",
173
            "http://swapi.dev/api/people/21/",
174
            "http://swapi.dev/api/people/35/",
175
            "http://swapi.dev/api/people/36/",
176
            "http://swapi.dev/api/people/37/",
177
            "http://swapi.dev/api/people/38/",
178
            "http://swapi.dev/api/people/39/",
179
            "http://swapi.dev/api/people/42/",
180
            "http://swapi.dev/api/people/60/",
181
            "http://swapi.dev/api/people/61/",
182
            "http://swapi.dev/api/people/66/"
183
        ],
184
        "films": [
185
            "http://swapi.dev/api/films/3/",
186
            "http://swapi.dev/api/films/4/",
187
            "http://swapi.dev/api/films/5/",
188
            "http://swapi.dev/api/films/6/"
189
        ],
190
        "created": "2014-12-10T11:52:31.066000Z",
191
        "edited": "2014-12-20T20:58:18.430000Z",
192
        "url": "http://swapi.dev/api/planets/8/"
193
    },
194
    {
195
        "name": "Coruscant",
196
        "rotation_period": "24",
197
        "orbital_period": "368",
198
        "diameter": "12240",
199
        "climate": "temperate",
200
        "gravity": "1 standard",
201
        "terrain": "cityscape, mountains",
202
        "surface_water": "unknown",
203
        "population": "1000000000000",
204
        "residents": [
205
            "http://swapi.dev/api/people/34/",
206
            "http://swapi.dev/api/people/55/",
207
            "http://swapi.dev/api/people/74/"
208
        ],
209
        "films": [
210
            "http://swapi.dev/api/films/3/",
211
            "http://swapi.dev/api/films/4/",
212
            "http://swapi.dev/api/films/5/",
213
            "http://swapi.dev/api/films/6/"
214
        ],
215
        "created": "2014-12-10T11:54:13.921000Z",
216
        "edited": "2014-12-20T20:58:18.432000Z",
217
        "url": "http://swapi.dev/api/planets/9/"
218
    },
219
    {
220
        "name": "Dagobah",
221
        "rotation_period": "23",
222
        "orbital_period": "341",
223
        "diameter": "8900",
224
        "climate": "murky",
225
        "gravity": "N/A",
226
        "terrain": "swamp, jungles",
227
        "surface_water": "8",
228
        "population": "unknown",
229
        "residents": [],
230
        "films": [
231
            "http://swapi.dev/api/films/2/",
232
            "http://swapi.dev/api/films/3/",
233
            "http://swapi.dev/api/films/6/"
234
        ],
235
        "created": "2014-12-10T11:42:22.590000Z",
236
        "edited": "2014-12-20T20:58:18.425000Z",
237
        "url": "http://swapi.dev/api/planets/5/"
238
    }
239
]);

1
// Run an aggregation to view total planet populations, grouped by climate type.
2
use('union-walkthrough');
3
4
db.lonely_planets.aggregate([
5
    {
6
        $match: {
7
            population: { $ne: 'unknown' }
8
        }
9
    },
10
    { 
11
        $unionWith: { 
12
            coll: 'popular_planets',
13
            pipeline: [{
14
                $match: {
15
                    population: { $ne: 'unknown' }
16
                }
17
            }] 
18
        } 
19
    },
20
    {
21
        $group: {
22
            _id: '$climate', totalPopulation: { $sum: { $toLong: '$population' } }
23
        }
24
    }
25
]);

1
[
2
  {
3
    _id: 'arid',
4
    totalPopulation: 200000
5
  },
6
  {
7
    _id: 'temperate',
8
    totalPopulation: 1007536000000
9
  },
10
  {
11
    _id: 'temperate, tropical',
12
    totalPopulation: 1000
13
  }
14
]

1
{
2
    $match: {
3
        population: { $ne: 'unknown' }
4
    }
5
},

1
{ 
2
    $unionWith: { 
3
        coll: 'popular_planets',
4
        pipeline: [
5
            {
6
                $match: {
7
                    population: { $ne: 'unknown' }
8
                }
9
            }
10
        ] 
11
    } 
12
},

1
{
2
    $group: {
3
        _id: '$climate', 
4
        totalPopulation: { $sum: { $toLong: '$population' } }
5
    }
6
}

1
// Run an aggregation with no pipeline
2
use('union-walkthrough');
3
4
db.lonely_planets.aggregate([
5
    { $unionWith: 'popular_planets' }
6
]);

1
use('union-walkthrough');

1
// Insert a few documents into the starships collection
2
db.starships.insertMany([
3
    {
4
        "name": "Death Star",
5
        "model": "DS-1 Orbital Battle Station",
6
        "manufacturer": "Imperial Department of Military Research, Sienar Fleet Systems",
7
        "cost_in_credits": "1000000000000",
8
        "length": "120000",
9
        "max_atmosphering_speed": "n/a",
10
        "crew": 342953,
11
        "passengers": 843342,
12
        "cargo_capacity": "1000000000000",
13
        "consumables": "3 years",
14
        "hyperdrive_rating": 4.0,
15
        "MGLT": 10,
16
        "starship_class": "Deep Space Mobile Battlestation",
17
        "pilots": []
18
    },
19
    {
20
        "name": "Millennium Falcon",
21
        "model": "YT-1300 light freighter",
22
        "manufacturer": "Corellian Engineering Corporation",
23
        "cost_in_credits": "100000",
24
        "length": "34.37",
25
        "max_atmosphering_speed": "1050",
26
        "crew": 4,
27
        "passengers": 6,
28
        "cargo_capacity": 100000,
29
        "consumables": "2 months",
30
        "hyperdrive_rating": 0.5,
31
        "MGLT": 75,
32
        "starship_class": "Light freighter",
33
        "pilots": [
34
            "http://swapi.dev/api/people/13/",
35
            "http://swapi.dev/api/people/14/",
36
            "http://swapi.dev/api/people/25/",
37
            "http://swapi.dev/api/people/31/"
38
        ]
39
    },
40
    {
41
        "name": "Y-wing",
42
        "model": "BTL Y-wing",
43
        "manufacturer": "Koensayr Manufacturing",
44
        "cost_in_credits": "134999",
45
        "length": "14",
46
        "max_atmosphering_speed": "1000km",
47
        "crew": 2,
48
        "passengers": 0,
49
        "cargo_capacity": 110,
50
        "consumables": "1 week",
51
        "hyperdrive_rating": 1.0,
52
        "MGLT": 80,
53
        "starship_class": "assault starfighter",
54
        "pilots": []
55
    },
56
    {
57
        "name": "X-wing",
58
        "model": "T-65 X-wing",
59
        "manufacturer": "Incom Corporation",
60
        "cost_in_credits": "149999",
61
        "length": "12.5",
62
        "max_atmosphering_speed": "1050",
63
        "crew": 1,
64
        "passengers": 0,
65
        "cargo_capacity": 110,
66
        "consumables": "1 week",
67
        "hyperdrive_rating": 1.0,
68
        "MGLT": 100,
69
        "starship_class": "Starfighter",
70
        "pilots": [
71
            "http://swapi.dev/api/people/1/",
72
            "http://swapi.dev/api/people/9/",
73
            "http://swapi.dev/api/people/18/",
74
            "http://swapi.dev/api/people/19/"
75
        ]
76
    },
77
]);
78
79
// Insert a few documents into the vehicles collection
80
db.vehicles.insertMany([
81
    {
82
        "name": "Sand Crawler",
83
        "model": "Digger Crawler",
84
        "manufacturer": "Corellia Mining Corporation",
85
        "cost_in_credits": "150000",
86
        "length": "36.8 ",
87
        "max_atmosphering_speed": 30,
88
        "crew": 46,
89
        "passengers": 30,
90
        "cargo_capacity": 50000,
91
        "consumables": "2 months",
92
        "vehicle_class": "wheeled",
93
        "pilots": []
94
    },
95
    {
96
        "name": "X-34 landspeeder",
97
        "model": "X-34 landspeeder",
98
        "manufacturer": "SoroSuub Corporation",
99
        "cost_in_credits": "10550",
100
        "length": "3.4 ",
101
        "max_atmosphering_speed": 250,
102
        "crew": 1,
103
        "passengers": 1,
104
        "cargo_capacity": 5,
105
        "consumables": "unknown",
106
        "vehicle_class": "repulsorcraft",
107
        "pilots": [],
108
    },
109
    {
110
        "name": "AT-AT",
111
        "model": "All Terrain Armored Transport",
112
        "manufacturer": "Kuat Drive Yards, Imperial Department of Military Research",
113
        "cost_in_credits": "unknown",
114
        "length": "20",
115
        "max_atmosphering_speed": 60,
116
        "crew": 5,
117
        "passengers": 40,
118
        "cargo_capacity": 1000,
119
        "consumables": "unknown",
120
        "vehicle_class": "assault walker",
121
        "pilots": [],
122
        "films": [
123
            "http://swapi.dev/api/films/2/",
124
            "http://swapi.dev/api/films/3/"
125
        ],
126
        "created": "2014-12-15T12:38:25.937000Z",
127
        "edited": "2014-12-20T21:30:21.677000Z",
128
        "url": "http://swapi.dev/api/vehicles/18/"
129
    },
130
    {
131
        "name": "AT-ST",
132
        "model": "All Terrain Scout Transport",
133
        "manufacturer": "Kuat Drive Yards, Imperial Department of Military Research",
134
        "cost_in_credits": "unknown",
135
        "length": "2",
136
        "max_atmosphering_speed": 90,
137
        "crew": 2,
138
        "passengers": 0,
139
        "cargo_capacity": 200,
140
        "consumables": "none",
141
        "vehicle_class": "walker",
142
        "pilots": [
143
            "http://swapi.dev/api/people/13/"
144
        ]
145
    },
146
    {
147
        "name": "Storm IV Twin-Pod cloud car",
148
        "model": "Storm IV Twin-Pod",
149
        "manufacturer": "Bespin Motors",
150
        "cost_in_credits": "75000",
151
        "length": "7",
152
        "max_atmosphering_speed": 1500,
153
        "crew": 2,
154
        "passengers": 0,
155
        "cargo_capacity": 10,
156
        "consumables": "1 day",
157
        "vehicle_class": "repulsorcraft",
158
        "pilots": [],
159
    }
160
]);

1
// Run an aggregation with no pipeline and differing schemas
2
use('union-walkthrough');
3
4
db.starships.aggregate([
5
  { $unionWith: 'vehicles' }
6
]);

1
[
2
  {
3
    _id: 5f306ddca3ee8339643f137e,
4
    name: 'Death Star',
5
    model: 'DS-1 Orbital Battle Station',
6
    manufacturer: 'Imperial Department of Military Research, Sienar Fleet Systems',
7
    cost_in_credits: '1000000000000',
8
    length: '120000',
9
    max_atmosphering_speed: 'n/a',
10
    crew: 342953,
11
    passengers: 843342,
12
    cargo_capacity: '1000000000000',
13
    consumables: '3 years',
14
    hyperdrive_rating: 4,
15
    MGLT: 10,
16
    starship_class: 'Deep Space Mobile Battlestation',
17
    pilots: []
18
  },
19
  {
20
    _id: 5f306ddca3ee8339643f137f,
21
    name: 'Millennium Falcon',
22
    model: 'YT-1300 light freighter',
23
    manufacturer: 'Corellian Engineering Corporation',
24
    cost_in_credits: '100000',
25
    length: '34.37',
26
    max_atmosphering_speed: '1050',
27
    crew: 4,
28
    passengers: 6,
29
    cargo_capacity: 100000,
30
    consumables: '2 months',
31
    hyperdrive_rating: 0.5,
32
    MGLT: 75,
33
    starship_class: 'Light freighter',
34
    pilots: [
35
      'http://swapi.dev/api/people/13/',
36
      'http://swapi.dev/api/people/14/',
37
      'http://swapi.dev/api/people/25/',
38
      'http://swapi.dev/api/people/31/'
39
    ]
40
  },
41
  // + 7 other results, omitted for brevity
42
]

1
use('union-walkthrough');
2
3
db.starships.aggregate([
4
    { 
5
        $unionWith: {
6
            coll: 'vehicles',
7
            pipeline: [
8
                { 
9
                    $redact: {
10
                        $cond: {
11
                            if: { $eq: [ "$manufacturer", "Kuat Drive Yards, Imperial Department of Military Research"] },
12
                            then: "$$PRUNE",
13
                            else: "$$DESCEND"
14
                        }
15
                    }
16
                }
17
            ]
18
        }
19
    }
20
]);

1
// Pipeline used with the vehicle collection
2
{ 
3
    $redact: {
4
        $cond: {
5
            if: { $eq: [ "$manufacturer", "Kuat Drive Yards, Imperial Department of Military Research"] },
6
            then: "$$PRUNE",
7
            else: "$$DESCEND"
8
        }
9
    }
10
}

1
[
2
  {
3
    _id: 5f306ddca3ee8339643f137e,
4
    name: 'Death Star',
5
    model: 'DS-1 Orbital Battle Station',
6
    manufacturer: 'Imperial Department of Military Research, Sienar Fleet Systems',
7
    cost_in_credits: '1000000000000',
8
    length: '120000',
9
    max_atmosphering_speed: 'n/a',
10
    crew: 342953,
11
    passengers: 843342,
12
    cargo_capacity: '1000000000000',
13
    consumables: '3 years',
14
    hyperdrive_rating: 4,
15
    MGLT: 10,
16
    starship_class: 'Deep Space Mobile Battlestation',
17
    pilots: []
18
  },
19
  {
20
    _id: 5f306ddda3ee8339643f1383,
21
    name: 'X-34 landspeeder',
22
    model: 'X-34 landspeeder',
23
    manufacturer: 'SoroSuub Corporation',
24
    cost_in_credits: '10550',
25
    length: '3.4 ',
26
    max_atmosphering_speed: 250,
27
    crew: 1,
28
    passengers: 1,
29
    cargo_capacity: 5,
30
    consumables: 'unknown',
31
    vehicle_class: 'repulsorcraft',
32
    pilots: []
33
  },
34
  // + 5 more non-Imperial manufactured results, omitted for brevity
35
]

1
// Invalid aggregation (tried to use sharded collection with $unionWith)
2
db.lonely_planets.aggregate([
3
    {
4
      $lookup: {
5
          from: "extinct_planets",
6
          let: { last_known_population: "$population", years_extinct: "$time_extinct" },
7
          pipeline: [
8
            // Filter criteria
9
            { $unionWith: { coll: "questionable_planets", pipeline: [ { pipeline } ] } },
10
            // Other pipeline stages
11
          ],
12
          as: "planetdata"
13
      }
14
    }
15
])