SQL Server 2016: Error When Using JSON_MODIFY on Nested JSON Objects
I've been working on this all day and I need help solving I'm attempting to set up I've spent hours debugging this and I'm integrating two systems and I'm getting frustrated with I tried several approaches but none seem to work..... I'm encountering an error when trying to update nested JSON objects in SQL Server 2016 using the `JSON_MODIFY` function. I have a table `Employees` with a column `Details` that contains JSON data. The structure of the JSON looks like this: ```json { "name": "John Doe", "address": { "street": "123 Main St", "city": "Springfield" }, "tags": ["developer", "sql"] } ``` I need to update the `city` field within the nested `address` object to "Shelbyville". I tried the following SQL query: ```sql UPDATE Employees SET Details = JSON_MODIFY(Details, '$.address.city', 'Shelbyville') WHERE id = 1; ``` However, I’m getting the error message: ``` Msg 195, Level 15, State 1, Line 1 'JSON_MODIFY' is not a recognized built-in function name. ``` I have verified that `JSON_MODIFY` is indeed available in SQL Server 2016, and I can confirm that the `Details` column is of type `nvarchar(max)`. I also checked that my database compatibility level is set to 130. What could be causing this issue? I’ve also tried executing the query in a new query window and restarting SQL Server Management Studio, but I still get the same error. Any insights would be appreciated! What's the best practice here? My team is using Sql for this web app. Any feedback is welcome! The stack includes Sql and several other technologies. How would you solve this? Any pointers in the right direction? Any examples would be super helpful. This is happening in both development and production on macOS. Could someone point me to the right documentation?