mongodb中3个排序规则之间的链式嵌入关系

Question

我正试图通过mongodb中的嵌入关系从3个集合中获取数据。

user_collection:

{
_id:1,
name:"Noah",
city_id:2,
job_id:3
},
{
_id:2,
name:"Oliver",
city_id:2,
job_id:1
},
{
_id:3,
name:"William",
city_id:1,
job_id:3
},

city_collection:

{
_id:1,
name:"Tokyo",
},
{
_id:2,
name:"Mexico City",
},
{
_id:3,
name:"Los Angeles",
},

job_collection:

{
_id:1,
title:"Architect",
},
{
_id:2,
title:"Barista",
},
{
_id:3,
title:"Farmer",
},

我有这3个集合，想按城市列出我的用户，并在返回的JSON 的用户部分设置他们的工作

我想获取一个嵌套的JSON。

city_collection到user_collection到job_collection

如下：

[
{
_id:1,
name:"Tokyo",
users:[{
_id:3,
name:"William",
job:[{
_id:1,
title:"Architect",
}]
}]

},
{
_id:2,
name:"Mexico City",
users:[{
_id:1,
name:"Noah",
job:[{
_id:3,
title:"Farmer",
}]
},
{
_id:2,
name:"Oliver",
job:[{
_id:1,
title:"Architect",
}]
}]

},
{
_id:3,
name:"Los Angeles",
users:[]

},
]

我使用$lookup，但它没有返回我想要的内容。

Answer 1

使用双$lookup

db.city.aggregate([
{
$lookup: {
from: "user",
localField: "_id",
foreignField: "city_id",
as: "users",
pipeline: [
{
$lookup: {
from: "job",
localField: "job_id",
foreignField: "_id",
as: "jobs"
}
}
]
}
}
])

mongoplayground