通过MONGO_FDW在Postgres映射MongoDB嵌套JSON集合：遇错能否用单JSON列？

完全可以用单个JSON（或JSONB）类型列存储MongoDB集合的整行文档，这正是解决你遇到的BSON-类型转换错误的高效方案。

创建单JSON列外部表的SQL示例 #

直接将整个MongoDB文档映射为Postgres的JSON/JSONB类型，无需逐个字段定义：

-- 用JSON类型存储
CREATE FOREIGN TABLE mongo_your_collection (
    full_doc json
)
SERVER mongo_fdw_server -- 替换成你已创建的mongo_fdw服务器名
OPTIONS (
    dbname 'your_mongo_db', -- 替换为MongoDB数据库名
    collection 'your_target_collection' -- 替换为目标集合名
);

-- 若需更好的查询性能和索引支持，推荐用JSONB类型
CREATE FOREIGN TABLE mongo_your_collection_bson (
    full_doc jsonb
)
SERVER mongo_fdw_server
OPTIONS (
    dbname 'your_mongo_db',
    collection 'your_target_collection'
);

如何查询数据 #

你可以直接查询完整文档，或用Postgres的JSON函数提取嵌套字段：

-- 查询全部文档
SELECT full_doc FROM mongo_your_collection;

-- 提取嵌套字段（比如文档中user下的name字段）
SELECT full_doc->'user'->>'name' AS user_name
FROM mongo_your_collection
WHERE full_doc->>'status' = 'active';

为什么这个方案能解决你的错误 #

原来的错误是因为手动映射字段时，MongoDB的嵌套BSON结构（比如数组、多层嵌套对象）无法直接匹配Postgres的常规数据类型（如varchar、int等）。而用JSON/JSONB类型直接存储整份文档，mongo_fdw会自动完成BSON到JSON的转换，跳过了单个字段的类型匹配环节，从根源避免了转换错误。

额外注意事项 #

• JSONB比JSON更适合频繁查询的场景，它支持GIN索引，能大幅提升嵌套字段的查询效率。
• Postgres提供了丰富的JSON处理函数（如jsonb_array_elements用于解析数组、jsonb_set用于修改字段等），可以满足大部分MongoDB文档的查询和操作需求。
• 确保你的mongo_fdw版本在1.3及以上，较旧版本可能对JSON类型的支持有限。

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