Removing Keys from JSON Documents in MySQL

Remove a Key from a JSON Document using JSON_REMOVE

Write a MySQL query to remove the key "warranty" from the JSON document in the Details column of the Products table using the JSON_REMOVE function

Solution:

-- Remove the "warranty" key from the JSON Details column for a specific product.

-- Specify the table to update.
UPDATE Products

-- Use JSON_REMOVE to delete the "warranty" key from the JSON object in the Details column.
-- JSON_REMOVE removes the specified key and its associated value from the JSON object.
SET Details = JSON_REMOVE(Details, '$.warranty')

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

Explanation:

• Purpose of the Query:
• To remove an unwanted or outdated key from a JSON document.
• Demonstrates how to modify JSON data by eliminating a specific element.
• Key Components:
• JSON_REMOVE(Details, '$.warranty') : Removes the key "warranty" from the JSON document.
• WHERE ProductName = 'Smartphone' : Targets a specific record for update.
• Real-World Application:
• Ideal for cleaning up JSON data by removing unnecessary fields from records.

Notes:

• Ensure that the JSON path accurately points to the key that needs removal.
• The operation only affects rows that match the specified condition.

For more Practice: Solve these Related Problems:

• Write a MySQL query to remove a deeply nested key from a JSON document for selected rows.
• Write a MySQL query to remove multiple keys from a JSON document in one operation.
• Write a MySQL query to remove a key only if its value is null in the JSON column.
• Write a MySQL query to conditionally remove a key from a JSON document based on another column's value.

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

Previous MySQL Exercise: Search for a Specific Value in JSON Using JSON_CONTAINS.
Next MySQL Exercise: Replace a Value in a JSON Document using JSON_REPLACE.