如何在 MongoDB 中进行嵌套 $lookup 搜索? [英] How to do nested $lookup search in MongoDB?
问题描述
我有 3 个收藏:
- 职位:
<上一页>+------------------+----------+----------------------+|position_id |company_id |位置名 |+------------------+----------+----------------------+|1 |1 |位置 1 |+------------------+----------+----------------------+|2 |2 |位置 2 |+------------------+----------+----------------------+
- 公司:
<上一页>+------------------+----------+----------------------+|company_id |行业ID |公司名称 |+------------------+----------+----------------------+|1 |1 |公司 1 |+------------------+----------+----------------------+|2 |2 |公司 2 |+------------------------------------------------------------------+
- 行业:
<上一页>+------------------+----------+|行业ID |行业名称 |+------------------+----------+|1 |工业1 |+------------------+----------+|2 |工业2 |+------------------+----------+
我需要在一个 API 中返回以下结果:
<代码>[{position_id: 1,position_name: '位置 1',公司: {公司编号:1,company_name: '公司 1',行业: {行业ID:1,行业名称:'行业 1',}}}, {position_id: 2,posiiton_name: '位置 2',公司: {company_id: 2,company_name: '公司 2',行业: {行业ID:2,行业名称:'行业 2',}}}]
所以我能想到的管道部分的代码如下:
const pipelines = [{$查找:{来自:公司",localField: 'company_id',foreignField: 'company_id',如:公司",$查找:{来自:行业",localField: 'industry_id',foreignField: 'industry_id',如:行业"}}}]返回位置.聚合(管道);
但这会引发一些错误.那么在 mongodb 搜索中进行嵌套 $lookup 的正确方法是什么?
提前致谢!
$lookup
3.6 语法允许您加入嵌套表和 $unwind
到从输入文档中解构一个数组字段,为每个元素输出一个文档.像这样的
position.aggregate([{ "$查找": {来自":公司","让": { "companyId": "$company_id" },管道":[{ "$match": { "$expr": { "$eq": ["$_id", "$$companyId" ] } } },{ "$查找": {来自":行业",让":{industry_id":$industry_id"},管道":[{ "$match": { "$expr": { "$eq": ["$_id", "$$industry_id" ] } } }],作为":行业"}},{ "$unwind": "$industry" }],作为":公司"}},{ "$unwind": "$company" }])
3.4版本
position.aggregate([{ "$查找": {来自":公司","localField": "company_id","foreignField": "_id",作为":公司"}},{ "$unwind": "$companies" },{ "$查找": {来自":行业","localField": "companies.industry_id","foreignField": "_id",作为":公司.行业"}},{ "$unwind": "$companies.industry" },{$组":{"_id": "$_id",公司":{$push":$公司"}}}])
I have 3 collections:
- positions:
+------------------+----------------------+-----------------------+ | position_id | company_id | position_name | +------------------+----------------------+-----------------------+ | 1 | 1 | position 1 | +------------------+----------------------+-----------------------+ | 2 | 2 | position 2 | +------------------+----------------------+-----------------------+
- companies:
+------------------+----------------------+-----------------------+ | company_id | industry_id | company_name | +------------------+----------------------+-----------------------+ | 1 | 1 | company 1 | +------------------+----------------------+-----------------------+ | 2 | 2 | company 2 | +-----------------------------------------------------------------+
- industries:
+------------------+----------------------+ | industry_id | industry_name | +------------------+----------------------+ | 1 | industry 1 | +------------------+----------------------+ | 2 | industry 2 | +------------------+----------------------+
I need to return the following result in one API:
[{
position_id: 1,
position_name: 'position 1',
company: {
company_id: 1,
company_name: 'company 1',
industry: {
industry_id: 1,
industry_name: 'industry 1',
}
}
}, {
position_id: 2,
posiiton_name: 'position 2',
company: {
company_id: 2,
company_name: 'company 2',
industry: {
industry_id: 2,
industry_name: 'industry 2',
}
}
}]
So the code of the pipeline part I can think of is like the following:
const pipelines = [{
$lookup: {
from: 'companies',
localField: 'company_id',
foreignField: 'company_id',
as: 'company',
$lookup: {
from: 'industries',
localField: 'industry_id',
foreignField: 'industry_id',
as: 'industry'
}
}
}]
return positions.aggregate(pipelines);
But this would throw some errors. So what is the correct way to do the nested $lookup in mongodb search?
Thanks in advance!
$lookup
3.6 syntax allows you to join nested tables and $unwind
to deconstructs an array field from the input documents to output a document for each element. Something like this
position.aggregate([
{ "$lookup": {
"from": "companies",
"let": { "companyId": "$company_id" },
"pipeline": [
{ "$match": { "$expr": { "$eq": [ "$_id", "$$companyId" ] } } },
{ "$lookup": {
"from": "industries",
"let": { "industry_id": "$industry_id" },
"pipeline": [
{ "$match": { "$expr": { "$eq": [ "$_id", "$$industry_id" ] } } }
],
"as": "industry"
}},
{ "$unwind": "$industry" }
],
"as": "company"
}},
{ "$unwind": "$company" }
])
With the 3.4 version
position.aggregate([
{ "$lookup": {
"from": "companies",
"localField": "company_id",
"foreignField": "_id",
"as": "companies"
}},
{ "$unwind": "$companies" },
{ "$lookup": {
"from": "industries",
"localField": "companies.industry_id",
"foreignField": "_id",
"as": "companies.industry"
}},
{ "$unwind": "$companies.industry" },
{ "$group": {
"_id": "$_id",
"companies": { "$push": "$companies" }
}}
])
这篇关于如何在 MongoDB 中进行嵌套 $lookup 搜索?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!