CODEWITHSUNDEEP
sql-serverjsonsql-server-2016
Edgars Pivovarenoks
Edgars Pivovarenoks

Reputation: 1684

Modify JSON properties with T-SQL in SQL Server 2016

Lets say I have JSON data :

DECLARE @json NVARCHAR(4000)
SET @json = 
N'{
    "info":{  
      "type":1,

      "address":{  
        "town":"Bristol",
        "county":"Avon",
        "country":"England"
      },
      "tags":["Sport", "Water polo"]
   },
   "type":"Basic"
}'

Is there any standard way to manipulate JSON properties in SQL Server 2016 T-SQL (add new, rename, remove), something similar to what JObject offers?

Upvotes: 1

Views: 1751

Answers (2)

Nathan_R
Nathan_R

Reputation: 36

To modify data, it is necessary to use the JSON_MODIFY statement with the JSON file as an input, as well as a necessary property and a new value. It's quite easy. If you want to add a new value to the data array, you can use the key word append. If you want to delete any property in the file, you need to specify the null value in the lax mode. You can find also code samples on this page http://codingsight.com/sql-server-2016-json-data-modification-part-4/

Upvotes: 2

Edgars Pivovarenoks
Edgars Pivovarenoks

Reputation: 1684

To answer my own question, with JSON_MODIFY I was able to add, delete properties.

"Rename" can be achieved by combining JSON_VALUE & JSON_MODIFY methods, essentially adding new property with old data, and then deleting old property.

Snippet :

-- Rename propety
DECLARE @stats NVARCHAR(100)='{"click_count": 173}'
PRINT @stats
SET @stats= JSON_MODIFY(JSON_MODIFY(@stats,'$.click_count_new', JSON_VALUE(@stats,'$.click_count')) ,'$.click_count', NULL)
PRINT @stats

Upvotes: 3

Related Questions