Azure MongoDB+Synapse Link：字符串列值意外包含JSON结构

当使用Azure Synapse的OPENROWSET查询Mongo API的Cosmos DB时，字符串字段返回{"string":"2020-12-14"}这类嵌套结构，核心原因是Mongo API的Cosmos DB基于BSON格式存储数据。在部分场景下（比如特定导入工具、旧版驱动或非标准数据写入流程），字符串会被包装在带BSON类型标识的结构中。Synapse的OPENROWSET默认读取原始的BSON序列化JSON表示，不会自动提取内层的实际值。

1. 修正数据存储格式（推荐） #

从根源解决问题，确保数据以Mongo原生字符串类型存储：

• 检查导入工具的配置，避免工具自动添加BSON类型标记（部分JSON导入工具会将字符串包装为{"string": "值"}以兼容特定BSON类型逻辑）。
• 通过Mongo驱动写入数据时，直接使用原生字符串类型，不要用自定义的类型包装对象。

2. 查询时解析字段 #

若无法修改存储的数据，可在查询中手动提取字段值：

单个字段解析（你已用到的方法） #

SELECT TOP 10 JSON_VALUE([date_rep], '$.string') AS [date_rep]
FROM OPENROWSET(
                PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=analytcstest;Database=ecdc',
                OBJECT = 'ecds',
                SERVER_CREDENTIAL = 'analytcstest'
)  with ( date_rep varchar(200) ) as rows

批量解析所有字段 #

结合OPENJSON批量展开所有嵌套字段：

SELECT TOP 10
    JSON_VALUE(doc.'$.date_rep', '$.string') AS date_rep,
    JSON_VALUE(doc.'$.country', '$.string') AS country,
    -- 其他字段按此格式依次添加
    JSON_VALUE(doc.'$.cases', '$.number') AS cases
FROM OPENROWSET(
                PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=analytcstest;Database=ecdc',
                OBJECT = 'ecds',
                SERVER_CREDENTIAL = 'analytcstest'
) as rows
CROSS APPLY OPENJSON(rows.BulkColumn) AS doc

3. 配置分析存储类型映射 #

若启用了Cosmos DB分析存储，可通过调整索引策略确保类型正确映射：

• 确认分析存储的索引配置中，字符串字段被标记为原生字符串类型，而非嵌套JSON结构。
• 使用OPENROWSET时，确保连接字符串正确指向分析存储（若单独启用），而非主存储。

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