如何在MongoDB中展平properties嵌套字段并创建视图

需求说明 #

我正在使用MongoDB Compass，现有文档结构如下：

{
  "_id": {
    "$oid": "639a1d134338565900901bc2"
  },
  "eventId": 6,
  "userId": {
    "$oid": "636d3ffb7d4cf8067dedc5d4"
  },
  "createdAt": {
    "$date": {
      "$numberLong": "1671044371692"
    }
  },
  "lastModified": {
    "$date": {
      "$numberLong": "1671132870160"
    }
  },
  "type": "Feature",
  "geometry": {
    "coordinates": [
      -71.820159,
      41.877574
    ],
    "type": "Point"
  },
  "properties": {
    "timestamp": {
      "$date": {
        "$numberLong": "1671044350325"
      }
    },
    "forms": [
      {
        "formId": 14,
        "field0": "CFS1 Fire",
        "field1": "Dispatched",
        "field2": null,
        "_id": {
          "$oid": "639a1d134338565900901bc3"
        }
      },
      {
        "formId": 13,
        "field1": "test",
        "_id": {
          "$oid": "639b76c64338565900901c42"
        }
      }
    ]
  },
  "favoriteUserIds": [],
  "states": [
    {
      "name": "active",
      "userId": {
        "$oid": "636d3ffb7d4cf8067dedc5d4"
      },
      "_id": {
        "$oid": "639a1d134338565900901bc4"
      }
    }
  ],
  "attachments": [],
  "__v": 1
}

需要创建新视图，将properties.forms数组中的字段展平到文档顶层，每个form的字段添加索引前缀（如第1个form的字段命名为formId1、form_1_field0，第2个为formId2、form_2_field1），同时保留properties中的timestamp，处理后结构如下：

{
  "_id": {
    "$oid": "639a1d134338565900901bc2"
  },
  "eventId": 6,
  "userId": {
    "$oid": "636d3ffb7d4cf8067dedc5d4"
  },
  "createdAt": {
    "$date": {
      "$numberLong": "1671044371692"
    }
  },
  "lastModified": {
    "$date": {
      "$numberLong": "1671132870160"
    }
  },
  "type": "Feature",
  "geometry": {
    "coordinates": [
      -71.820159,
      41.877574
    ],
    "type": "Point"
  },
  "formId1": 14,
  "form_1_field0": "CFS1 Fire",
  "form_1_field1": "Dispatched",
  "form_1_field2": null,
  "form_1__id": {"$oid": "639a1d134338565900901bc3"},
  "formId2": 13,
  "form_2_field1": "test",
  "form_2__id": {"$oid": "639b76c64338565900901c42"},
  "properties": {
    "timestamp": {
      "$date": {
        "$numberLong": "1671044350325"
      }
    }
  },
  "favoriteUserIds": [],
  "states": [
    {
      "name": "active",
      "userId": {
        "$oid": "636d3ffb7d4cf8067dedc5d4"
      },
      "_id": {
        "$oid": "639a1d134338565900901bc4"
      }
    }
  ],
  "attachments": [],
  "__v": 1
}

注：原目标文档中重复的_id字段会导致键冲突，这里调整为form_1__id、form_2__id避免该问题。

实现步骤（MongoDB Compass聚合管道） #

可以通过Compass的聚合功能实现，以下是完整的聚合阶段：

1. 添加form索引并精简properties字段
   使用$addFields为每个form添加数组下标+1的索引，同时将properties字段替换为仅保留timestamp的内容：

   {
     $addFields: {
       "indexedForms": {
         $map: {
           input: "$properties.forms",
           as: "form",
           in: {
             index: { $add: [{ $indexOfArray: ["$properties.forms", "$$form"] }, 1] },
             form: "$$form"
           }
         }
       },
       "properties": "$properties.timestamp"
     }
   }
   

2. 转换form字段为带前缀的键值对
   使用$addFields结合$objectToArray和$arrayToObject，将每个form的字段转换为带索引前缀的键值对象：

   {
     $addFields: {
       "formFields": {
         $map: {
           input: "$indexedForms",
           as: "item",
           in: {
             $arrayToObject: {
               $map: {
                 input: { $objectToArray: "$$item.form" },
                 as: "field",
                 in: {
                   k: {
                     $cond: {
                       if: { $eq: ["$$field.k", "formId"] },
                       then: { $concat: ["formId", { $toString: "$$item.index" }] },
                       else: { $concat: ["form_", { $toString: "$$item.index" }, "_", "$$field.k"] }
                     }
                   },
                   v: "$$field.v"
                 }
               }
             }
           }
         }
       }
     }
   }
   

3. 合并所有字段到文档顶层
   使用$replaceRoot结合$mergeObjects，将原始文档、处理后的properties和所有form字段合并：

   {
     $replaceRoot: {
       newRoot: {
         $mergeObjects: [
           "$$ROOT",
           { $arrayToObject: { $reduce: { input: "$formFields", initialValue: [], in: { $concatArrays: ["$$value", { $objectToArray: "$$this" }] } } } },
           { properties: { timestamp: "$properties" } }
         ]
       }
     }
   }
   

4. 清理临时字段
   使用$project移除聚合过程中生成的临时字段：

   {
     $project: {
       indexedForms: 0,
       formFields: 0
     }
   }
   

在MongoDB Compass中操作 #

1. 打开Compass，连接到目标数据库和集合
2. 点击顶部的聚合选项卡
3. 依次添加上述4个聚合阶段，每个阶段选择对应的操作（$addFields、$replaceRoot、$project）并粘贴对应JSON代码
4. 预览结果符合要求后，点击保存为视图，命名视图即可

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