How to change value in random element of array in json in mysql 8?

@dalton_moen 

In MySQL 8, you can use the JSON_REPLACE function along with the JSON_ARRAY_ELEMENT function to change the value in a random element of an array in JSON.

Here's an example of how you can do it:

1. Create a table with a JSON column:

1
2
3
4

CREATE TABLE my_table (
  id INT,
  json_data JSON
);

1. Insert some sample data:

1
2

INSERT INTO my_table (id, json_data)
VALUES (1, '["apple", "banana", "cherry", "date"]');

1. Update the value in a random element using JSON_REPLACE:

1
2
3
4
5
6
7
8
9

UPDATE my_table
SET json_data = JSON_REPLACE(
                  json_data,
                  JSON_ARRAY_ELEMENT(
                    json_data,
                    FLOOR(RAND() * (JSON_LENGTH(json_data) - 1)) + 1),
                  'new_value'
                )
WHERE id = 1;

In the above example, the JSON_ARRAY_ELEMENT function is used to select a random element from the array. The FLOOR(RAND() * (JSON_LENGTH(json_data) - 1)) + 1 expression generates a random index within the bounds of the array. The JSON_REPLACE function then replaces the selected element with the desired new value.

Note: The RAND() function returns a random value between 0 and 1, so multiplying it with (JSON_LENGTH(json_data) - 1) gives a random index within the range of the array elements.

After executing the update query, the value in a random element of the JSON array will be changed to 'new_value'.