在mongodb中进行汇总查找后，如何填充ID的深层嵌套数组? [英] How to populate deeply nested array of ids after aggregate lookup in mongodb?

问题描述

此问题是上一个问题.

我在A，B和C馆藏中有以下文件

I have following documents in collection A, B, and C

  "A": 
    {_id: "A_id1", labelA: "LabelA1"},
    {_id: "A_id2", labelA: "LabelA2"}
  

  "C": 
    { _id: "C_id1", labelC: "LabelC1"},
    { _id: "C_id2",labelC: "LabelC2"}
  
  "B":
    {
      _id: "B_id1",
      labelB: "LabelB1",
      refToA: "A_id1",
      items: [
        {
          itemLabel: "a",
          options: [ { optionLabel: "opt1", codes: [ "C_id1"] },
                     { optionLabel: "opt2", codes: [ "C_id2"] } ]
        }
      ]
    },
    
    {
      _id: "B_id4",
      labelB: "LabelB4",
      refToA: "A_id2",
      items: [
        { itemLabel: "b",
          options: [ { optionLabel: "opt3", codes: [ "C_id1", "C_id2"]
        }
      ]
    }

集合B在项目"字段中嵌套了子文档数组，进一步在子项目.options"中嵌套了子子文档的arrya.最后，第三个子级别的"items.options.codes"包含文档C的ID列表.

The collection B has nested array of sub-documents in the field 'items', further nested arrya of sub-sub-document as 'items.options'. Finally, the third sub-level 'items.options.codes' contain list of ids of document C.

我想聚合A来收集所有引用A的B.我使用以下命令来做到这一点:

I want to Aggregate A to collect all B as that refer to A. I do it using the command:

db.A.aggregate([
  {
    $match: {
      _id: "A_id1"
    }
  },
  {
    $lookup: {
      from: "B",
      let: {
        refToA: "$_id"
      },
      pipeline: [
        {
          $match: { $expr: { $eq: ["$refToA", "$$refToA"]}}
        },          
      ],
      as: "BCollection"
    }
  }
])

给出以下结果

  {
    "BCollection": [
      {
        "_id": "B_id1",
        "items": [
          {
            "itemLabel": "a",
            "options": [
              {
                "codes": [ "C_id1" ],
                "optionLabel": "opt1"
              },
              {
                "codes": [ "C_id2"],
                "optionLabel": "opt2"
              }
            ]
          }
        ],
        "labelB": "LabelB1",
        "refToA": "A_id1"
      }
    ],
    "_id": "A_id1",
    "labelA": "LabelA1"
  }

现在，我想保留上面的结构，并使用集合C中的详细信息填充代码"字段.所需结果如下

Now, I want to preserve the above structure and also populate the field 'codes' with details from collection C. The desired result is as follows

  {
    "BCollection": [
      {
        "_id": "B_id1",
        "items": [
          {
            "itemLabel": "a",
            "options": [
              {
                "codes": [ { _id: "C_id1", labelC: "LabelC1"} ],
                "optionLabel": "opt1"
              },
              {
                "codes": [ { _id: "C_id2", labelC: "LabelC2"}],
                "optionLabel": "opt2"
              }
            ]
          }
        ],
        "labelB": "LabelB1",
        "refToA": "A_id1"
      }
    ],
    "_id": "A_id1",
    "labelA": "LabelA1"
  }

我尝试了以下查询，但未产生期望的结果:

I have tried the following query, but it does not produce the desired result:

db.A.aggregate([
  {
    $match: {
      _id: "A_id1"
    }
  },
  {
    $lookup: {
      from: "B",
      let: {
        refToA: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$refToA",
                "$$refToA"
              ]
            }
          }
        },
        {
          $lookup: {
            from: "C",
            localField: "items.options.codes",
            foreignField: "_id",
            as: "items.option.codes"
          }
        }
      ],
      as: "BCollection"
    }
  }
])

您可以在此处查看上述查询的输出: https://mongoplayground.net/p/ZJVU6PQF6MZ

You can see the output of above query in here: https://mongoplayground.net/p/ZJVU6PQF6MZ

推荐答案

尝试一下:

db.A.aggregate([
    {
        $lookup: {
            from: "B",
            let: { refToA: "$_id" },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$refToA", "$$refToA"] }
                    }
                },
                { $unwind: "$items" },
                { $unwind: "$items.options" },
                {
                    $lookup: {
                        from: "C",
                        localField: "items.options.codes",
                        foreignField: "_id",
                        as: "items.options.codes"
                    }
                },
                {
                    $group: {
                        _id: {
                            id: "$_id",
                            itemLabel: "$items.itemLabel"
                        },
                        labelB: { $first: "$labelB" },
                        refToA: { $first: "$refToA" },
                        items: {
                            $push: {
                                "itemLabel": "$items.itemLabel",
                                "options": "$items.options"
                            }
                        }
                    }
                },
                {
                    $group: {
                        _id: "$_id.id",
                        labelB: { $first: "$labelB" },
                        refToA: { $first: "$refToA" },
                        items: {
                            $push: {
                                itemLabel: "$_id.itemLabel",
                                "options": "$items.options"
                            }
                        }
                    }
                }
            ],
            as: "BCollection"
        }
    }
]);

这篇关于在mongodb中进行汇总查找后，如何填充ID的深层嵌套数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！