Updating JSON Documents in MySQL

Update a JSON Document with JSON_SET

Write a MySQL query to update a value within a JSON document stored in a column using the JSON_SET function.

Solution:

-- Update the model in the JSON details of a specific product.
UPDATE Products
SET Details = JSON_SET(Details, '$.model', 'X200')
WHERE ProductName = 'Smartphone';

Explanation:

• Purpose of the Query:
• To modify the "model" attribute within the JSON data for a given product.
• Illustrates how to update parts of a JSON document without replacing the entire document.
• Key Components:
• JSON_SET(Details, '$.model', 'X200') : Updates the "model" key with a new value.
• WHERE ProductName = 'Smartphone' : Specifies which record to update.
• Real-World Application:
• Useful when product specifications change or need to be corrected in the database.

Notes:

• JSON_SET allows for partial updates of a JSON document.
• Ensure that the JSON structure remains valid after the update.

For more Practice: Solve these Related Problems:

• Write a MySQL query to update a nested key value within a JSON document stored in a column.
• Write a MySQL query to update an element inside a JSON array in a JSON column.
• Write a MySQL query to update a JSON document only if a specific key exists in the JSON structure.
• Write a MySQL query to update several nested keys simultaneously within a JSON document using one statement.

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

Previous MySQL Exercise: Query JSON Data with JSON_EXTRACT.
Next MySQL Exercise: Validate JSON Data using JSON_VALID.