How to use JSON_MODIFY in SQL Server

This article is to share with you about how to use JSON_MODIFY in SQL Server. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

The use of JSON_MODIFY in SQL Server

Intro

SQL Server has supported some JSON operations since 2016. Recently, many fields in the project have been saved directly into JSON. You need to know how to operate JSON in SQL Server.

JSON support is available for SQL Server 2016 and above and Azure SQL Database.

Recently, many fields in the project store a JSON string, especially for data with many uncertain attributes. We usually store a JSON string in a field such as Extra. Because of the great uncertainty, we are likely to encounter the problem of modifying the structure of the JSON string. JSON_MODIFY is used when modifying a value of the JSON object and the structure of the JSON. Today, let's introduce the use of JSON_MODIFY.

Example

Let's look directly at the example:

-- it is applicable for append to append elements PRINT JSON_MODIFY (@ testJsonValue, 'append $.neighbors', 'Azure') to the array;-- invalid usage, which will update the attribute to the string PRINT JSON_MODIFY (@ testJsonValue,' $.marker','["C#", "T-SQL", "Azure"]'). -- modify the array PRINT JSON_MODIFY (@ testJsonValue,'$. Likes', JSON_QUERY ('[["C #", "T-SQL", "Azure"]');-- modify an element of the array PRINT JSON_MODIFY (@ testJsonValue,'$.Likes [0]', 'CSharp');-- add generic nested PRINT JSON_MODIFY (@ testJsonValue,' $.Likes', JSON_QUERY ('{"Girl": "Alice"}')) PRINT JSON_MODIFY (@ testJsonValue,'$.likes', JSON_QUERY ('{"Animals": ["Dog", "Cat"]}');-- add bool attribute PRINT JSON_MODIFY (@ testJsonValue,'$.ShowAwardsAfterQuizEnds', CONVERT (BIT, 'false')); PRINT JSON_MODIFY (@ testJsonValue,' $.ShowAwardsAfterQuizEnds', CONVERT (BIT, 1));-- add int attribute PRINT JSON_MODIFY (@ testJsonValue,'$TotalWintalnersCount', CONVERT (INT, '20')) PRINT JSON_MODIFY (@ testJsonValue,'$.TotalWinnersCount', 20);-- Delete the attribute PRINT JSON_MODIFY (@ testJsonValue,'$.name', NULL)

The output is shown in the following figure:

More

Note:

If the field to be updated is not a JSON object or an array, you need to use JSON_QUERY, otherwise the corresponding property of the update will become a string. If you want to delete an attribute, update it to NULL. If you want to add an attribute of type bool, you need to convert the corresponding value to type BIT.

The above is how to use JSON_MODIFY in SQL Server. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.