MongoDB内嵌对象检索需求：按client_name搜索表单响应及结构优化

现有数据库结构 #

[
    {
        "_id": ObjectId("628fb1db596e46baf54fb8fc"),
        "current_version": 1,
        "form_name": "Attack on forms",
        "history": [
            {
                "responses": [
                    {
                        "client_id": "99",
                        "client_name": "Foo Baar",
                        "values": {
                            "values": [{"daora": "man"}]
                        }
                    }
                ],
                "version": 0
            },
            {
                "responses": [
                    {
                        "client_id": "66",
                        "client_name": "Irwin",
                        "values": {
                            "values": [{"x1": "x1"}]
                        }
                    },
                    {
                        "client_id": "77",
                        "client_name": "Levi",
                        "values": {
                            "values": [{"x1": "x1"}]
                        }
                    }
                ],
                "version": 1
            }
        ],
        "id": "3faef4ec-a6ea-40d4-8c8d-a5fb14cb2e4b",
        "is_active": true,
        "user_id": "3003"
    },
    {
        "_id": ObjectId("628fb2668aa89decf6b88d18"),
        "current_version": 1,
        "form_name": "u suario as b",
        "history": [
            {
                "responses": [
                    {
                        "client_id": "66",
                        "client_name": "Irwin",
                        "values": {
                            "values": [{"xxxxx": "xxxxx"}]
                        }
                    }
                ],
                "version": 0
            },
            {
                "responses": [
                    {
                        "client_id": "66",
                        "client_name": "Irwin",
                        "values": {
                            "values": [{"ccccc": "ccccc"}]
                        }
                    }
                ],
                "version": 1
            }
        ],
        "id": "1c728313-38ea-4ae7-9750-a3dc3f9c02bd",
        "is_active": true,
        "user_id": "3003"
    }
]

检索功能实现 #

1. 指定表单当前版本的client_name前缀检索 #

需求：针对指定表单ID，在其current_version对应的历史版本中，按client_name前缀模糊匹配（支持匹配"Irwin"、"Irw"、"Irwi"这类前缀）。

以表单ID为"3faef4ec-a6ea-40d4-8c8d-a5fb14cb2e4b"为例，MongoDB查询语句如下：

db.forms.aggregate([
    // 匹配指定表单
    {$match: {id: "3faef4ec-a6ea-40d4-8c8d-a5fb14cb2e4b"}},
    // 拆解history数组，过滤出当前版本记录
    {$unwind: "$history"},
    {$match: {"history.version": {$eq: "$current_version"}}},
    // 拆解responses数组，过滤前缀匹配的client_name
    {$unwind: "$history.responses"},
    {$match: {"history.responses.client_name": {$regex: "^Irw", $options: "i"}}},
    // 重组结构返回目标响应数据
    {$group: {
        _id: null,
        responses: {$push: "$history.responses"}
    }},
    {$project: {_id: 0, responses: 1}}
])

预期返回结果：

{
    "responses": [
        {
            "client_id": "66",
            "client_name": "Irwin",
            "values": {
                "values": [{"x1": "x1"}]
            }
        }
    ]
}

2. 所有表单当前版本的client_name前缀检索 #

需求：遍历所有表单，在各自的current_version版本中按client_name前缀模糊匹配，返回包含匹配响应的表单完整结构（仅保留当前版本的历史记录及匹配的响应）。

MongoDB查询语句如下：

db.forms.aggregate([
    // 拆解history数组，过滤当前版本
    {$unwind: "$history"},
    {$match: {"history.version": {$eq: "$current_version"}}},
    // 拆解responses数组，过滤前缀匹配的client_name
    {$unwind: "$history.responses"},
    {$match: {"history.responses.client_name": {$regex: "^Irw", $options: "i"}}},
    // 重组每个表单的响应数组
    {$group: {
        _id: "$_id",
        current_version: {$first: "$current_version"},
        form_name: {$first: "$form_name"},
        id: {$first: "$id"},
        is_active: {$first: "$is_active"},
        user_id: {$first: "$user_id"},
        responses: {$push: "$history.responses"}
    }},
    // 重组history结构
    {$project: {
        current_version: 1,
        form_name: 1,
        history: [{version: "$current_version", responses: "$responses"}],
        id: 1,
        is_active: 1,
        user_id: 1
    }}
])

预期返回结果：

[
    {
        "_id": ObjectId("628fb1db596e46baf54fb8fc"),
        "current_version": 1,
        "form_name": "Attack on forms",
        "history": [
            {
                "responses": [
                    {
                        "client_id": "66",
                        "client_name": "Irwin",
                        "values": {
                            "values": [{"x1": "x1"}]
                        }
                    }
                ],
                "version": 1
            }
        ],
        "id": "3faef4ec-a6ea-40d4-8c8d-a5fb14cb2e4b",
        "is_active": true,
        "user_id": "3003"
    },
    {
        "_id": ObjectId("628fb2668aa89decf6b88d18"),
        "current_version": 1,
        "form_name": "u suario as b",
        "history": [
            {
                "responses": [
                    {
                        "client_id": "66",
                        "client_name": "Irwin",
                        "values": {
                            "values": [{"ccccc": "ccccc"}]
                        }
                    }
                ],
                "version": 1
            }
        ],
        "id": "1c728313-38ea-4ae7-9750-a3dc3f9c02bd",
        "is_active": true,
        "user_id": "3003"
    }
]

数据库结构优化建议 #

• 拆分响应数据与表单主体：将history.responses拆分为独立集合，通过form_id和version关联表单。避免表单文档过大导致的查询性能下降，同时单独对响应数据建索引更高效。
• 添加前缀索引：如果频繁做client_name前缀模糊查询，建议在client_name字段创建前缀索引（如db.responses.createIndex({client_name: 1})），大幅提升检索速度。
• 冗余当前版本响应：在表单文档中直接存储current_responses字段，无需每次查询都从history过滤当前版本，减少聚合操作复杂度，提升查询效率。
• 简化values结构：原数据中values.values嵌套冗余，可简化为values: [{"x1": "x1"}]或直接存储键值对对象，降低数据嵌套层级与解析成本。

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