将Cosmos DB的NoSQL查询迁移至Azure Synapse时遇语法错误求助

问题背景 #

一段可在Azure Cosmos DB正常运行的嵌套数组查询，迁移到Azure Synapse使用OPENROWSET连接CosmosDB时，因数组元素取值逻辑报错**“value”附近有语法错误**，仅获取完整NewData列时可正常执行。

原Cosmos DB可正常运行的查询 #

SELECT 
c.TrfId
, c.NewData[0]["value"] AS "NodeID"
, c.NewData[1]["value"] AS "FilePath"

FROM c
WHERE c.NewData <> null

Synapse中报错的查询 #

SELECT *
FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=account;Database=database',
                OBJECT = 'object',
                SERVER_CREDENTIAL = 'servercred'
) WITH (

[TrfId] varchar(256)
, [NewData][0]["value"] varchar(max) AS "ECMNodeID"
, [NewData][1]["value"] varchar(256) AS "ECMFilePath"

) AS [data]

WHERE [NewData] is not null    
GO

数据示例 #

{
    "TrfId": "10_000436",
    "NewData": [
        {
            "key": "ECM ID",
            "value": "233462908"
        },
        {
            "key": "ECM file path",
            "value": "2022-Feb-21.pdf"
        }
    ]
}

错误原因 #

Synapse的OPENROWSET的WITH子句仅支持定义顶层列的数据类型，不允许直接在列定义中使用数组索引+对象属性访问的嵌套语法，必须先将整个JSON数组列定义为字符串/JSON类型，再在SELECT阶段进行取值处理。

修正后的查询方案 #

方案1：使用JSON_VALUE直接提取固定位置的值 #

先将NewData定义为字符串类型存储JSON，再通过JSON路径提取指定元素：

SELECT 
    TrfId,
    JSON_VALUE(NewData, '$[0].value') AS ECMNodeID,
    JSON_VALUE(NewData, '$[1].value') AS ECMFilePath
FROM OPENROWSET(
    PROVIDER = 'CosmosDB',
    CONNECTION = 'Account=account;Database=database',
    OBJECT = 'object',
    SERVER_CREDENTIAL = 'servercred'
) WITH (
    TrfId varchar(256),
    NewData varchar(max)
) AS [data]
WHERE NewData IS NOT NULL    
GO

方案2：使用OPENJSON解析数组（适合动态结构） #

如果数组元素顺序不固定，可通过OPENJSON展开数组，再按key匹配取值：

SELECT 
    d.TrfId,
    MAX(CASE WHEN j.[key] = 'ECM ID' THEN j.[value] END) AS ECMNodeID,
    MAX(CASE WHEN j.[key] = 'ECM file path' THEN j.[value] END) AS ECMFilePath
FROM OPENROWSET(
    PROVIDER = 'CosmosDB',
    CONNECTION = 'Account=account;Database=database',
    OBJECT = 'object',
    SERVER_CREDENTIAL = 'servercred'
) WITH (
    TrfId varchar(256),
    NewData varchar(max)
) AS d
CROSS APPLY OPENJSON(d.NewData) WITH (
    [key] varchar(100),
    [value] varchar(256)
) AS j
WHERE d.NewData IS NOT NULL
GROUP BY d.TrfId
GO

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