Updating Multiple JSON Keys in MySQL

Update Multiple Values in a JSON Column Using JSON_SET

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

Solution:

-- Update multiple keys in the JSON Details for a specific product.

-- Specify the table to update.
UPDATE Products

-- Use JSON_SET to update multiple fields in the JSON Details column.
-- JSON_SET modifies the JSON object by updating or adding the specified key-value pairs.
SET Details = JSON_SET(Details,
                       '$.model', 'X300',       -- Update the 'model' field to 'X300'.
                       '$.warranty', '3 years')  -- Update the 'warranty' field to '3 years'.

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

Explanation:

• Purpose of the Query:
• To modify multiple attributes within a JSON document in a single operation.
• Illustrates how JSON_SET can be used to update several keys simultaneously.
• Key Components:
• JSON_SET(Details, '$.model', 'X300', '$.warranty', '3 years') : Updates both the "model" and "warranty" keys.
• WHERE ProductName = 'Smartphone' : Filters the update to the targeted product.
• Real-World Application:
• Useful for batch updating product details or correcting multiple data points in the JSON document.

Notes:

• Ensure that the JSON document remains valid after multiple updates.
• This method streamlines updates by reducing the need for multiple queries.

For more Practice: Solve these Related Problems:

• Write a MySQL query to update multiple nested keys in a JSON document only if they satisfy a specific condition.
• Write a MySQL query to update several keys in the JSON column across multiple rows simultaneously.
• Write a MySQL query to update keys in a JSON document where the new values include different data types.
• Write a MySQL query to update multiple keys in a JSON document using values derived from a subquery.

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

Previous MySQL Exercise: Aggregate Data into a JSON Array with JSON_ARRAYAGG.
Next MySQL Exercise: Retrieve JSON Object Keys Using JSON_KEYS.