Modify JSON properties with T-SQL in SQL Server 2016

Question

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?

You mean like JSON_MODIFY, as documented in the Fine Manual? — Jeroen Mostert
– Jeroen Mostert, Commented Apr 13, 2017 at 11:46
Bingo, at first glance it looked like you can modify only values, not properties. — Edgars Pivovarenoks
– Edgars Pivovarenoks, Commented Apr 13, 2017 at 12:16

Edgars Pivovarenoks · Accepted Answer · 2017-04-13 12:20:30Z

3

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

answered Apr 13, 2017 at 12:20

Edgars Pivovarenoks

1,7321 gold badge20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nathan_R · Accepted Answer · 2017-06-01 15:37:28Z

2

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/

answered Jun 1, 2017 at 15:37

Nathan_R

362 bronze badges

Collectives™ on Stack Overflow

Modify JSON properties with T-SQL in SQL Server 2016

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related