Replacing Values in JSON Documents in MySQL

Replace a Value in a JSON Document using JSON_REPLACE

Write a MySQL query to replace the value of the "model" key in the details JSON column of the Products table using the JSON_REPLACE function.

Solution:

-- Replace the "model" value in the JSON Details for a specific product.

-- Specify the table to update.
UPDATE Products

-- Use JSON_REPLACE to update the value of the "model" key in the JSON object in the Details column.
-- JSON_REPLACE modifies the value of an existing key in the JSON object.
SET Details = JSON_REPLACE(Details, '$.model', 'X400')

-- Apply the update only to the product with the ProductName 'Smartphone'.
WHERE ProductName = 'Smartphone'; 

Explanation:

• Purpose of the Query:
• To update an existing key’s value within a JSON document without altering the rest of the document.
• Demonstrates the use of JSON_REPLACE for targeted JSON modifications.
• Key Components:
• JSON_REPLACE(Details, '$.model', 'X400') : Changes the value of "model" to 'X400'.
• WHERE ProductName = 'Smartphone' : Specifies the record to update.
• Real-World Application:
• Useful when a product update requires changing a specific attribute stored in JSON.

Notes:

• JSON_REPLACE only updates existing keys; if the key does not exist, no change is made.
• Validate that the JSON structure is maintained after the update.

For more Practice: Solve these Related Problems:

• Write a MySQL query to replace a value in a nested JSON object only if the key exists.
• Write a MySQL query to update multiple keys in a JSON document simultaneously using JSON_REPLACE.
• Write a MySQL query to replace a JSON value conditionally based on a subquery result.
• Write a MySQL query to replace a JSON key’s value while ensuring no update occurs if the key is missing.

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

Previous MySQL Exercise: Remove a Key from a JSON Document using JSON_REMOVE.
Next MySQL Exercise: Extract JSON Array Length Using JSON_LENGTH.