MongoDB同一文档关联查询：替换categories的ID为measureValues对象

我有如下精简后的MongoDB文档：

{
  "sections": [
    {
      "_id": {
        "$oid": "64cbeb62b669cd29a5719a8f"
      },
      "categories": [
        {
          "measureValues": [
            "64cbeb62b669cd29a5719a95",
            "64cbeb62b669cd29a5719a99"
          ]
        }
      ]
    }
  ],
  "measures": [
    {
      "_id": {
        "$oid": "64cbeb62b669cd29a5719a93"
      },
      "name": {
        "64cbeb62b669cd29a5719a92": {
          "nodeType": "nodeName",
          "value": "measure-1"
        }
      },
      "measureValues": [
        {
          "_id": {
            "$oid": "64cbeb62b669cd29a5719a95"
          },
          "measureValues": {
            "nodeName": {
              "64cbeb62b669cd29a5719a94": {
                "nodeType": "nodeName",
                "value": "measureValue",
                "_class": "com.myapp.cqrsdemo.model.DataObj"
              }
            },
            "comment": {
              "64cbeb62b669cd29a5719a97": {
                "nodeType": "comment",
                "value": "this is a comment",
                "_class": "com.myapp.cqrsdemo.model.DataObj"
              }
            }
          },
          "categoriesIds": [
            "64cbeb62b669cd29a5719a91"
          ],
          "measureName": "measure-1"
        },
        {
          "_id": {
            "$oid": "64cbeb62b669cd29a5719a99"
          },
          "measureValues": {
            "nodeName": {
              "64cbeb62b669cd29a5719a98": {
                "nodeType": "nodeName",
                "value": "measureValue",
                "_class": "com.myapp.cqrsdemo.model.DataObj"
              }
            },
            "date": {
              "64cbeb62b669cd29a5719a9b": {
                "nodeType": "date",
                "value": "2023-02-02",
                "_class": "com.myapp.cqrsdemo.model.DataObj"
              }
            }
          },
          "categoriesIds": [
            "64cbeb62b669cd29a5719a91"
          ],
          "measureName": "measure-1"
        }
      ]
    }
  ],
  "_class": "com.myapp.cqrsdemo.model.ProjectGroup"
}

需要将sections.categories.measureValues中的ID，替换为measures.measureValues中匹配_id.$oid的完整对象，最终期望得到以下输出：

{
  "sections": [
    {
      "_id": {
        "$oid": "64cbeb62b669cd29a5719a8f"
      },
      "categories": [
        {
          "measureValues": [
            {
              "_id": {
                "$oid": "64cbeb62b669cd29a5719a95"
              },
              "measureValues": {
                "nodeName": {
                  "64cbeb62b669cd29a5719a94": {
                    "nodeType": "nodeName",
                    "value": "measureValue",
                    "_class": "com.myapp.cqrsdemo.model.DataObj"
                  }
                },
                "comment": {
                  "64cbeb62b669cd29a5719a97": {
                    "nodeType": "comment",
                    "value": "this is a comment",
                    "_class": "com.myapp.cqrsdemo.model.DataObj"
                  }
                }
              },
              "categoriesIds": [
                "64cbeb62b669cd29a5719a91"
              ],
              "measureName": "measure-1"
            },
            {
              "_id": {
                "$oid": "64cbeb62b669cd29a5719a99"
              },
              "measureValues": {
                "nodeName": {
                  "64cbeb62b669cd29a5719a98": {
                    "nodeType": "nodeName",
                    "value": "measureValue",
                    "_class": "com.myapp.cqrsdemo.model.DataObj"
                  }
                },
                "date": {
                  "64cbeb62b669cd29a5719a9b": {
                    "nodeType": "date",
                    "value": "2023-02-02",
                    "_class": "com.myapp.cqrsdemo.model.DataObj"
                  }
                }
              },
              "categoriesIds": [
                "64cbeb62b669cd29a5719a91"
              ],
              "measureName": "measure-1"
            }
          ]
        }
      ]
    }
  ],
  "_class": "com.myapp.cqrsdemo.model.ProjectGroup"
}

实现的聚合管道 #

db.collection.aggregate([
  {
    $addFields: {
      // 将所有measures中的measureValues合并为一个扁平数组，方便匹配
      allMeasureValues: {
        $reduce: {
          input: "$measures",
          initialValue: [],
          in: { $concatArrays: ["$$value", "$$this.measureValues"] }
        }
      }
    }
  },
  {
    $project: {
      "_class": 1,
      "sections": {
        $map: {
          input: "$sections",
          as: "section",
          in: {
            "_id": "$$section._id",
            "categories": {
              $map: {
                input: "$$section.categories",
                as: "category",
                in: {
                  "measureValues": {
                    $map: {
                      input: "$$category.measureValues",
                      as: "mvId",
                      in: {
                        // 从扁平数组中匹配对应ID的对象
                        $arrayElemAt: [
                          {
                            $filter: {
                              input: "$allMeasureValues",
                              cond: { $eq: ["$$this._id.$oid", "$$mvId"] }
                            }
                          },
                          0
                        ]
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

管道说明 #

• 第一步：通过$addFields和$reduce将嵌套在measures数组中的所有measureValues对象合并成一个扁平数组allMeasureValues，避免多层嵌套查找的复杂操作。
• 第二步：使用$project和多层$map遍历sections、categories和measureValues，对每个ID用$filter匹配allMeasureValues中对应的对象，再用$arrayElemAt取出匹配结果（因为ID唯一，结果只有一个），替换原ID值。
• 最终输出保留原文档的sections结构和_class字段，移除临时创建的allMeasureValues。

内容的提问来源于stack exchange，提问作者Pushpak