我正在使用Cosmos Data迁移工具在环境之间迁移数据。在迁移过程中,我需要更新数据中网站的主机名。我可以很容易地使用顶级对象数据进行这样的查询:
SELECT Farms["name"], Farms["farmerInfo"], REPLACE(Farms["websiteLink"], "thiswebsite", "newHostName") AS websiteLink FROM Farms
我的Cosmos DB数据的结构如下(数据仅用于示例(:
{
"name": "Red's Farm",
"websiteLink": "www.thiswebsite.com/goats/",
"farmerInfo": {
"name": "Bob",
"websiteLink": "www.thiswebsite.com/goats/",
"hasGoats": true,
"numGoats": 17
}
}
我实际上不需要修改任何顶级数据。我需要修改的数据在";farmerInfo";对象我试过几件事,但运气不好。如何使用SQL api替换此对象中的字符串?
我希望迁移后的数据看起来像这样:
{
"name": "Red's Farm",
"websiteLink": "www.thiswebsite.com/goats/",
"farmerInfo": {
"name": "Bob",
"websiteLink": "www.newHostName.com/goats/", <--- Updated data
"hasGoats": true,
"numGoats": 17
}
}
您可以在SELECT
语句中使用SELECT
语句来构建子对象。例如:
SELECT
c.name,
c.websiteLink,
(
SELECT
c.farmerInfo.name,
REPLACE(c.farmerInfo.websiteLink, "thiswebsite", "newHostName") AS websiteLink
) AS farmerInfo
FROM c