Modifying JSON Data in SQL with JSON_MODIFY

Parse and Modify JSON Data

Write a SQL query to update a specific field in a JSON column.

Solution:

-- Update a specific field in a JSON column.
UPDATE Employees
SET Profile = JSON_MODIFY(Profile, '$.Age', 35)
WHERE EmployeeID = 1;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to update a specific field (Age) in a JSON column (Profile) for a given employee.

2. Key Components :

1. JSON_MODIFY : Updates a specific key-value pair in a JSON object.
2. $.Age : Specifies the path to the field being updated.

3. Why use JSON_MODIFY? :

1. This function allows you to modify JSON data directly without rewriting the entire JSON object.

4. Real-World Application :

1. For example, in user profile management systems, you might use this query to update individual fields in a JSON-stored profile.

Additional Notes:

• Ensure that the JSON path is valid and matches the structure of the JSON data.
• Use this exercise to teach how to manipulate semi-structured data efficiently.

For more Practice: Solve these Related Problems:

• Write a SQL query to update the email address of a user stored in a JSON column named "Profile".
• Write a SQL query to increment the age field in a JSON object by 1 for all users born before 1990.
• Write a SQL query to add a new key-value pair ("isActive": true) to a JSON object in a user table.
• Write a SQL query to remove a specific skill from a nested JSON array in a profile column.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Use FIRST_VALUE and LAST_VALUE Functions.
Next SQL Exercise: Transform XML Data Using XQuery.