Скачать или смотреть Modify JSON Values with JSON_MODIFY without Looping in SQL Server

Learn how to efficiently update JSON values in SQL Server using `JSON_MODIFY` without looping through each item. Streamline your data manipulation process!
---
This video is based on the question https://stackoverflow.com/q/65970829/ asked by the user 'muth' ( https://stackoverflow.com/u/8003560/ ) and on the answer https://stackoverflow.com/a/65972892/ provided by the user 'Charlieface' ( https://stackoverflow.com/u/14868997/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: JSON_MODIFY all values without looping

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
A Simplified Approach to Modifying JSON Values in SQL Server

When working with JSON data in SQL Server, modifying specific values can often feel cumbersome, especially if you need to update multiple items without looping through them individually. This article addresses a common problem: how to modify all "disc" values within a JSON array to a specific number without using loops.

The Challenge

Imagine you have a JSON structure stored in a SQL Server table, and you want to change every "disc" value to 100. The sample JSON structure looks like this:

[[See Video to Reveal this Text or Code Snippet]]

Updating each "disc" value manually in a loop can be inefficient, especially if the array is large. So, how can you do this efficiently?

The Solution: Using JSON_MODIFY and OPENJSON

You can update the JSON values using SQL Server's built-in functions: JSON_MODIFY and OPENJSON. This approach allows you to achieve your goal without needing to loop through each array item. Here's a step-by-step guide on how to implement this solution.

Step 1: Prepare Your Table

To start, let's create a temporary table to hold the JSON data:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Update JSON Values

Now, we will use the following SQL command to update all "disc" values to 100:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

JSON_MODIFY: This function is used to modify JSON text by applying changes to specified properties.

OPENJSON: Parses the JSON text and retrieves the "items" array, allowing us to interact with its data structure.

CROSS APPLY: Combines the original JSON data with the modified values in one efficient query.

FOR JSON PATH: This clause helps to reassemble the items back into JSON format after modification.

Step 3: Verify the Changes

To check if the work was successful, you can run:

[[See Video to Reveal this Text or Code Snippet]]

The output should now reflect that all "disc" values have been updated to 100.

Conclusion

By using the combination of JSON_MODIFY and OPENJSON, you can efficiently modify JSON data in SQL Server without the need for looping through each item. This technique not only saves time but also enhances the performance of your SQL operations with JSON.

Using this approach can streamline your data manipulation tasks significantly. Give it a try with your JSON data, and see the efficiency it can offer!