Appending Data to JSON Arrays in MySQL

Append Data to a JSON Array Using JSON_ARRAY_APPEND

Write a MySQL query to append a new review to the Reviews JSON array in the Products table using the JSON_ARRAY_APPEND function.

Solution:

-- Append a new review to the Reviews JSON array for a specific product.

-- Specify the table to update.
UPDATE Products

-- Use JSON_ARRAY_APPEND to add a new review to the Reviews JSON array.
-- JSON_ARRAY_APPEND appends the specified value ('Excellent product!') to the end of the JSON array.
SET Reviews = JSON_ARRAY_APPEND(Reviews, '$', 'Excellent product!')

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

Explanation:

• Purpose of the Query:
• To add a new element to an existing JSON array stored in a column.
• Demonstrates the use of JSON_ARRAY_APPEND for dynamic array modifications.
• Key Components:
• JSON_ARRAY_APPEND(Reviews, '$', 'Excellent product!') : Appends the string to the end of the JSON array.
• WHERE ProductName = 'Smartphone' : Targets the specific product record.
• Real-World Application:
• Useful for recording additional customer reviews or comments in a flexible JSON format.

Notes:

• Ensure that the Reviews column already contains a JSON array; otherwise, the operation may fail.
• The '$' path indicates the root of the JSON array.

For more Practice: Solve these Related Problems:

• Write a MySQL query to append an element to a JSON array only if that element does not already exist.
• Write a MySQL query to append multiple elements to a JSON array in a single update operation.
• Write a MySQL query to conditionally append data to a JSON array based on a comparison with another column.
• Write a MySQL query to append a JSON object to an existing JSON array within a column.

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

Previous MySQL Exercise: Create a Table with a JSON Column.
Next MySQL Exercise: Convert Relational Data to JSON Array of Objects Using JSON_ARRAYAGG.