如何在 MongoDB 中进行嵌套 $lookup 搜索? [英] How to do nested $lookup search in MongoDB?

问题描述

我有 3 个收藏:

1. 职位:

<上一页>+------------------+----------+----------------------+|position_id |company_id |位置名 |+------------------+----------+----------------------+|1 |1 |位置 1 |+------------------+----------+----------------------+|2 |2 |位置 2 |+------------------+----------+----------------------+

1. 公司:

<上一页>+------------------+----------+----------------------+|company_id |行业ID |公司名称 |+------------------+----------+----------------------+|1 |1 |公司 1 |+------------------+----------+----------------------+|2 |2 |公司 2 |+------------------------------------------------------------------+

1. 行业:

<上一页>+------------------+----------+|行业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:

1. positions:

+------------------+----------------------+-----------------------+
|    position_id   |    company_id        |       position_name   |
+------------------+----------------------+-----------------------+
|         1        |        1             |        position 1     |
+------------------+----------------------+-----------------------+
|         2        |        2             |        position 2     |
+------------------+----------------------+-----------------------+

2. companies:

+------------------+----------------------+-----------------------+
|    company_id    |     industry_id      |       company_name    |
+------------------+----------------------+-----------------------+
|        1         |          1           |       company 1       |
+------------------+----------------------+-----------------------+
|        2         |          2           |       company 2       |
+-----------------------------------------------------------------+

3. 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屋！