如何在猫鼬中聚合嵌套的查找数组? [英] How to aggregate nested lookup array in mongoose?

问题描述

我在查找嵌套数组方面遇到问题，例如我有4个集合.

I have a problem with how to lookup nested array, for example i have 4 collections.

1. 用户集合

"user": [
    {
      "_id": "1234",
      "name": "Tony",
      "language": [
        {
          "_id": "111",
          "language_id": "919",
          "level": "Expert"
        },
        {
          "_id": "111",
          "language_id": "920",
          "level": "Basic"
        }
      ]
    }
  ]

1. 语言集合

"language": [
    {
      "_id": "919",
      "name": "English"
    },
    {
      "_id": "920",
      "name": "Chinese"
    }
  ]

1. 工作

"job": [
    {
      "_id": "10",
      "title": "Programmer",
      "location": "New York"
    }
  ],

1. CvSubmit集合

"cvsubmit": [
    {
      "_id": "11",
      "id_user": "1234",
      "id_job": "11"
    }
  ]

我的查询汇总是:

db.cvsubmit.aggregate([
  {
    $lookup: {
      from: "user",
      localField: "id_user",
      foreignField: "_id",
      as: "id_user"
    }
  },
  {
    $lookup: {
      from: "language",
      localField: "id_user.language.language_id",
      foreignField: "_id",
      as: "id_user.language.language_id"
    }
  },
])

但是结果是:

[
  {
    "_id": "11",
    "id_job": "11",
    "id_user": {
      "language": {
        "language_id": [
          {
            "_id": "919",
            "name": "English"
          },
          {
            "_id": "920",
            "name": "Chinese"
          }
        ]
      }
    }
  }
]

我想要这样的结果，还显示所有用户数据详细信息，例如 name :

I want the result like this, also showing all user data detail like name:

[
  {
    "_id": "11",
    "id_job": "11",
    "id_user": {
      "_id": "1234",
      "name": "Tony"
      "language": [
          {
            "_id": "919",
            "name": "English",
            "Level": "Expert"
          },
          {
            "_id": "920",
            "name": "Chinese",
            "level": "Basic"
          }
        ]
    }
  }
]

Mongo Playground链接 https://mongoplayground.net/p/i0yCucjruey

Mongo Playground link https://mongoplayground.net/p/i0yCucjruey

谢谢.

推荐答案

• $ lookup 和 user 集合
• $ unwind 解构 id_user 数组
• $ lookup 和 language 集合，并在 languages 字段中返回
• $ map 迭代 id_user.language 数组的外观
• $ reduce 迭代从集合返回的 languages 数组的循环，检查 language_id 是否匹配的条件，然后返回 name
• $lookup with user collection
• $unwind deconstruct id_user array
• $lookup with language collection and return in languages field
• $map to iterate look of id_user.language array
• $reduce to iterate loop of languages array returned from collection, check condition if language_id match then return name

db.cvsubmit.aggregate([
  {
    $lookup: {
      from: "user",
      localField: "id_user",
      foreignField: "_id",
      as: "id_user"
    }
  },
  { $unwind: "$id_user" },
  {
    $lookup: {
      from: "language",
      localField: "id_user.language.language_id",
      foreignField: "_id",
      as: "languages"
    }
  },
  {
    $addFields: {
      languages: "$$REMOVE",
      "id_user.language": {
        $map: {
          input: "$id_user.language",
          as: "l",
          in: {
            _id: "$$l._id",
            level: "$$l.level",
            name: {
              $reduce: {
                input: "$languages",
                initialValue: "",
                in: {
                  $cond: [
                    { $eq: ["$$this._id", "$$l.language_id"] },
                    "$$this.name",
                    "$$value"
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
])

游乐场

根据NoSQL，您的数据库结构不准确，最多应有2个集合，使用$ lookup和$ unwind进行的抢劫会导致性能问题.

You database structure is not accurate as per NoSQL, there should be max 2 collections, loot of join using $lookup and $unwind will cause performance issues.

这篇关于如何在猫鼬中聚合嵌套的查找数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！