如何在MongoDB中执行JOIN

关于MongoDB的全面概述以及我关于它的所有帖子，请查看我的概述。

如果你的MongoDB实例中有关系型数据，你可以执行类似于通常在SQL查询中进行的JOIN操作。

考虑一下下面的数据集:

db.podcasts.insertMany([
	{id: 1, name: "Off The Clock", category: "Technology", rss: "https://anchor.fm/s/76aafa5c/podcast/rss"},
	{id: 2, name: "Tech Over Tea", category: "Technology", rss: "https://anchor.fm/s/149fd51c/podcast/rss"}
]);

db.episodes.insertMany([
	{podcast_id: 1, title: "Resume Tips", published_on: "2022-01-11"},
	{podcast_id: 2, title: "#75 Welcome Our Hacker Neko Waifu | Cyan Nyan", published_on: "2021-08-04"},
	{podcast_id: 2, title: "Square Enix Refuses To Take My Money | Solo", published_on: "2022-01-26"},
	{podcast_id: 1, title: "Find the Right Job", published_on: "2022-01-25"}
]);

如果你想获得每个播客及其所有相关剧集，你可以使用$lookup 聚合阶段来实现。

聚合的结果是这样的:

db.podcasts.aggregate([
	{ $lookup: {
		from: "episodes", // Name of the other collection to "join" from
		localField: "id", // Name of the field your current documents contain to compare with
		foreignField: "podcast_id", // Name of field to compare to in the "from" collection's documents
		as: "episodes" // What to call the field that contains the array of sub documents that matched
	}}
]);

episodes 集合中的任何文件，如果其podcast_id 与podcasts 文件之一相匹配，就会被添加到一个称为episodes 的子文件数组中。

结果会是这样的:

[
	{
		id: 1,
	 	name: "Off The Clock",
		category: "Technology",
		rss: "https://anchor.fm/s/76aafa5c/podcast/rss",
		episodes: [
			{podcast_id: 1, title: "Resume Tips", published_on: "2022-01-11"},
			{podcast_id: 1, title: "Find the Right Job", published_on: "2022-01-25"}
		]
	},
	{
		id: 2,
		name: "Tech Over Tea",
		category: "Technology",
		rss: "https://anchor.fm/s/149fd51c/podcast/rss",
		episodes: [
			{podcast_id: 2, title: "#75 Welcome Our Hacker Neko Waifu | Cyan Nyan", published_on: "2021-08-04"},
			{podcast_id: 2, title: "Square Enix Refuses To Take My Money | Solo", published_on: "2022-01-26"},
		]
	}
]

这就相当于SQL查询:

SELECT
	*
FROM
	podcasts
LEFT JOIN episodes ON episodes.podcast_id = podcasts.id;

我在SQL的例子中指定了LEFT JOIN ，因为如果没有文件与$lookup 聚合阶段相匹配，播客仍然会被返回，episodes 字段是一个空数组。