首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >用于查找和合并嵌套文档的聚合管道

问用于查找和合并嵌套文档的聚合管道
EN

Stack Overflow用户
提问于 2020-08-04 08:53:21
回答 1查看 192关注 0票数 1

我正在努力编写聚合管道,以便在不覆盖数据中现有键/值的情况下,根据文档的_id查找嵌套文档并返回特定的名称。我已经成功地对嵌套数组执行了此操作,但无法对嵌套在嵌套数组中的数组执行此操作。

所以我想要查找每个配料和每个subIngredient的数量,并将它们与这些已经存在的配料的数据(即_id,subIngredient)合并。

这就是我到目前为止所拥有的:https://mongoplayground.net/p/ft4oIMm_8wg

产品集合:

代码语言:javascript
复制
[
    {
      "_id": {
        "$oid": "5ecf269bceb735416db0b329"
      },
      "id": 36,
      "title": "Product 1",
      "description": {
        "generalInformation": "Some information",
        "activeIngredients": [
          {
            "_id": 1636,
            "qty": 133.5,
            "measure": "µg",
            "subIngredient": [
              {
                "_id": 1626,
                "qty": 16.6,
                "measure": "µg"
              }
            ],
            
          },
          {
            "_id": 1234,
            "qty": 133.5,
            "measure": "µg",
            "subIngredient": [
              {
                "_id": 1122,
                "qty": 16.6,
                "measure": "µg"
              },
              {
                "_id": 1212,
                "qty": 16.6,
                "measure": "µg"
              }
            ],
            
          },
          
        ]
      },
      
    },
    {
      "_id": {
        "$oid": "5ecf269bceb735416db0b346"
      },
      "id": 36,
      "title": "Product 2",
      "description": {
        "generalInformation": "Some information",
        "activeIngredients": [
          {
            "_id": 1234,
            "qty": 133.5,
            "measure": "µg",
            "subIngredient": [
              {
                "_id": 1122,
                "qty": 16.6,
                "measure": "µg"
              }
            ],
            
          },
          {
            "_id": 1234,
            "qty": 133.5,
            "measure": "µg",
            "subIngredient": [
              {
                "_id": 1122,
                "qty": 16.6,
                "measure": "µg"
              },
              {
                "_id": 1212,
                "qty": 16.6,
                "measure": "µg"
              }
            ],
            
          },
          
        ]
      },
      
    }
  ]

配料集合:

代码语言:javascript
复制
[
    {
      "_id": 1234,
      "name": "Ingredient 1",
      
    },
    {
      "_id": 1122,
      "name": "Ingredient 2",
      
    },
    {
      "_id": 1212,
      "name": "Ingredient 3",
      
    },
    {
      "_id": 1636,
      "name": "Ingredient 4",
      
    },
    {
      "_id": 1626,
      "name": "Ingredient 5",
      
    }
  ]

应返回的内容:

代码语言:javascript
复制
[
  {
    "_id": ObjectId("5ecf269bceb735416db0b329"),
    "title": "Product 1"
    "description": {
      "activeIngredients": {
        "_id": 1636,
        "measure": "µg",
        "name": "Ingredient 4",
        "qty": 133.5,
        "subIngredient": [
          {
            "_id": 1626,
            "measure": "µg",
            "qty": 16.6
          }
        ]
      },
      "generalInformation": "Some information"
    },
    "ingredients": [
      {
        "_id": 1636,
        "measure": "µg",
        "name": "Ingredient 4",
        "qty": 133.5,
        "subIngredient": [
          {
            "_id": 1626,
            "measure": "µg",
            "qty": 16.6,
            "name": "Ingredient 2"
          }
        ]
      },
      {
        "_id": 1234,
        "measure": "µg",
        "name": "Ingredient 1",
        "qty": 133.5,
        "subIngredient": [
          {
            "_id": 1122,
            "measure": "µg",
            "qty": 16.6,
            "name": "Ingredient 2"
          },
          {
            "_id": 1212,
            "measure": "µg",
            "qty": 16.6,
            "name": "Ingredient 2"
          }
        ]
      }
    ]
    
  },
  
]

我目前的流水线:

代码语言:javascript
复制
[
  {
    "$unwind": {
      "path": "$description.activeIngredients",
      "preserveNullAndEmptyArrays": false
    }
  },
  {
    "$lookup": {
      "from": "ingredients",
      "localField": "description.activeIngredients._id",
      "foreignField": "_id",
      "as": "description.activeIngredients.name"
    }
  },
  {
    "$addFields": {
      "description.activeIngredients.name": {
        "$arrayElemAt": [
          "$description.activeIngredients.name.name",
          0
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "ingredients": {
        "$push": "$description.activeIngredients"
      },
      "description": {
        "$first": "$description"
      },
      "title": {
        "$first": "$title"
      }
    }
  },
  {
    "$lookup": {
      "from": "ingredients",
      "localField": "ingredients.subIngredient._id",
      "foreignField": "_id",
      "as": "subIngredients"
    }
  }
]

感谢任何人的帮助。谢谢。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-08-04 15:26:14

这并不远,您可以通过多种不同的方法来实现此结果,其中一种方法是只对subingredients数组执行$unwind操作,然后在该数组上执行$lookup操作,最后添加另一个$group阶段来重构文档。

正如你已经清楚地表明,你知道如何做所有这些事情,我将展示一种不同的方法,利用像$map,$indexOfArray和Mongo的v3.6 $lookup语法这样的运算符。

策略不是解开子数组,我们只需$lookup所有相关的子成分,然后使用我指定的运算符“合并”两个数组。

也就是说,采取:

代码语言:javascript
复制
[ {id: 5, name: "name"} ];
[ {id: 5, qty: 25} ]

并将其转化为:

代码语言:javascript
复制
[ {id: 5, name: "name", qty: 25} ]

我们是这样做的:

代码语言:javascript
复制
db.products.aggregate([
  {
    "$unwind": {
      "path": "$description.activeIngredients",
      "preserveNullAndEmptyArrays": false
    }
  },
  {
    "$lookup": {
      "from": "ingredients",
      "localField": "description.activeIngredients._id",
      "foreignField": "_id",
      "as": "description.activeIngredients.name"
    }
  },
  {
    "$addFields": {
      "description.activeIngredients.name": {
        "$arrayElemAt": [
          "$description.activeIngredients.name.name",
          0
        ]
      }
    }
  },
  {
    "$lookup": {
      "from": "ingredients",
      "let": {
        sub: "$description.activeIngredients.subIngredient"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              "$setIsSubset": [
                [
                  "$_id"
                ],
                {
                  $map: {
                    input: "$$sub",
                    as: "datum",
                    in: "$$datum._id"
                  }
                }
              ]
            }
          }
        }
      ],
      "as": "subIngredients"
    }
  },
  {
    "$addFields": {
      "description.activeIngredients.subIngredient": {
        $map: {
          input: "$description.activeIngredients.subIngredient",
          as: "sub",
          in: {
            "$mergeObjects": [
              "$$sub",
              {
                name: {
                  $arrayElemAt: [
                    "$subIngredients.name",
                    {
                      "$indexOfArray": [
                        "$subIngredients._id",
                        "$$sub._id"
                      ]
                    }
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "ingredients": {
        "$push": "$description.activeIngredients"
      },
      "description": {
        "$first": "$description"
      },
      "title": {
        "$first": "$title"
      }
    }
  }
])

MongoPlayground

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/63238809

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档