如何跨两个集合反向引用文档?

Question

我有一个person集合，文档如下:

_id: ObjectId('64103dd44c7b1355e4812e87'),
sport: 'Football',
name: 'Brian',
teams: [
ObjectId('62f63b38e6a6cbb9a58c58c4'),
ObjectId('64219e4eb5924e070a23f846')
]

---

_id: ObjectId('642ad97d0c945ff64f2a5f4c'),
sport: 'Football',
name: 'Marc',
teams: [
ObjectId('64219e4eb5924e070a23f846')
]

---

_id: ObjectId('64269d6c38364df7ca902d75'),
sport: 'Basketball',
name: 'Francis',
teams: [
ObjectId('641453c9adc10762db6997cb'),
ObjectId('64269ebc38364df7ca902e17')
]

然后我有这个teams集合，文档如下:

_id: ObjectId('62f63b38e6a6cbb9a58c58c4'),
sport: 'Football',
name: 'Team A',
persons: [
ObjectId('642ada46f785be5eb0042f63')
]

---

_id: ObjectId('64219e4eb5924e070a23f846'),
sport: 'Football',
name: 'Team B',
persons: [
]

---

_id: ObjectId('62f3a57bcc852536a665b179'),
sport: 'Football',
name: 'Team C',
persons: [
]

---

_id: ObjectId('641453c9adc10762db6997cb'),
sport: 'Basketball',
name: 'Team D',
persons: [
ObjectId('642ada848b8c7b48102c3cb4')
]

---

_id: ObjectId('64269ebc38364df7ca902e17'),
sport: 'Basketball',
name: 'Team E',
persons: [
ObjectId('642ada95f8f190e02582e10d')
]

现在我的团队在人员文档中被引用，我正在尝试创建一个基本上颠倒它的脚本，以便人员在团队文档中被引用。

请记住，在团队文档中已经有一些应该保留的现有引用。而且每次迁移不应该跨运动进行:例如，一个从事"足球"运动的人不应该迁移到"足球"以外的团队。

结果应该是这样的:

<标题>的人

_id: ObjectId('64103dd44c7b1355e4812e87'),
sport: 'Football',
name: 'Brian'

---

_id: ObjectId('642ad97d0c945ff64f2a5f4c'),
sport: 'Football',
name: 'Marc'

---

_id: ObjectId('64269d6c38364df7ca902d75'),
sport: 'Basketball',
name: 'Francis'

<标题>团队

_id: ObjectId('62f63b38e6a6cbb9a58c58c4'),
sport: 'Football',
name: 'Team A',
persons: [
ObjectId('62f641ae84f995495a0f567f')
]

---

_id: ObjectId('64219e4eb5924e070a23f846'),
sport: 'Football',
name: 'Team B',
persons: [
ObjectId('642ada46f785be5eb0042f63'),
ObjectId('64103dd44c7b1355e4812e87'),
ObjectId('642ad97d0c945ff64f2a5f4c')
]

---

_id: ObjectId('62f3a57bcc852536a665b179'),
sport: 'Football',
name: 'Team C',
persons: [
]

---

_id: ObjectId('641453c9adc10762db6997cb'),
sport: 'Basketball',
name: 'Team D',
persons: [
ObjectId('642ada848b8c7b48102c3cb4'),
ObjectId('64269d6c38364df7ca902d75')
]

---

_id: ObjectId('64269ebc38364df7ca902e17'),
sport: 'Basketball',
name: 'Team E',
persons: [
ObjectId('642ada95f8f190e02582e10d'),
ObjectId('64269d6c38364df7ca902d75')
]

我如何做到这一点?

Answer 1

对于teams集合，您可以运行如下查询:

db.teams.aggregate([
{$lookup: {
from: "persons",
localField: "_id",
foreignField: "teams",
pipeline: [{$project: {_id: 1}}],
as: "moreP"
}},
{$project: {
persons: {$concatArrays: [
"$persons",
{$map: {
input: "$moreP",
in: "$$this._id"
}}
]},
name: 1,
sport: 1
}},
{$merge: {into: "teams"}}
])

看看它在操场的例子中是如何工作的

后来：可以更新persons集合来取消teams键的设置:

db.persons.updateMany({}, {$unset: {teams: ""}})