Cosmos DB REPLACE strings in nested Object

Question

I'm using the Cosmos Data migration tool to migrate data between environments. During my migration, I need to update the hostname of the website in the data. I was able to do this pretty easily with a query like this with the top level object data:

SELECT Farms["name"], Farms["farmerInfo"], REPLACE(Farms["websiteLink"], "thiswebsite", "newHostName") AS websiteLink FROM Farms

My Cosmos DB data is structured like (data is just for the example):

{  
    "name": "Red's Farm",
    "websiteLink": "www.thiswebsite.com/goats/",
    "farmerInfo": {  
        "name":       "Bob",   
        "websiteLink":      "www.thiswebsite.com/goats/",   
        "hasGoats":    true,  
        "numGoats":    17

    }  
}  

I don't actually need to modify any of the top level data. The data I need to modify is within the "farmerInfo" object. I've tried a few things but I've had no luck. How can I replace a string in this object using the SQL api?

I want the data to look like this after the migration:

{  
        "name": "Red's Farm",
        "websiteLink": "www.thiswebsite.com/goats/",
        "farmerInfo": {  
            "name":       "Bob",   
            "websiteLink":      "www.newHostName.com/goats/",  <--- Updated data
            "hasGoats":    true,   
            "numGoats":    17
 
        }  
    }  

Answer 1

You can use a SELECT statement in your SELECT statement to build up the sub objects. As example:

SELECT 
    c.name,
    c.websiteLink,
    (
        SELECT 
            c.farmerInfo.name, 
            REPLACE(c.farmerInfo.websiteLink, "thiswebsite", "newHostName") AS websiteLink
    ) AS farmerInfo
FROM c