MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- extract second element
select json_extract('["A", "B", "C"]', '$[1]')
-- "B"

json_extract('["A", "B", "C"]', '$[1]')

"B"

-- replace second element
select json_set('["A", "B", "C"]', '$[1]', 'D')
-- ["A", "D", "C"]

json_set('["A", "B", "C"]', '$[1]', 'D')

["A", "D", "C"]

-- insert fourth element
select json_set('["A", "B", "C"]', '$[3]', 'E')
-- ["A", "B", "C", "E"]

json_set('["A", "B", "C"]', '$[3]', 'E')

["A", "B", "C", "E"]

-- attempt to insert second element fails as it already exists
select json_insert('["A", "B", "C"]', '$[1]', 'F')
-- ["A", "B", "C"]

json_insert('["A", "B", "C"]', '$[1]', 'F')

["A", "B", "C"]

-- use json_array_insert to insert a new second element and move the other elements right
select json_array_insert('["A", "B", "C"]', '$[1]', 'F')
-- ["A", "F", "B", "C"]

json_array_insert('["A", "B", "C"]', '$[1]', 'F')

["A", "F", "B", "C"]

-- insert fourth element
select json_insert('["A", "B", "C"]', '$[3]', 'F')
-- ["A", "B", "C", "F"]

json_insert('["A", "B", "C"]', '$[3]', 'F')

["A", "B", "C", "F"]

-- or use json_array_append to add an element at the end
select json_array_append('["A", "B", "C"]', '$', 'F')
-- ["A", "B", "C", "F"]

json_array_append('["A", "B", "C"]', '$', 'F')

["A", "B", "C", "F"]

-- replace second element
select json_replace('["A", "B", "C"]', '$[1]', 'G')
-- ["A", "G", "C"]

json_replace('["A", "B", "C"]', '$[1]', 'G')

["A", "G", "C"]

-- attempt to replace non-existing element fails
select json_replace('["A", "B", "C"]', '$[3]', 'G')
-- ["A", "B", "C"]

json_replace('["A", "B", "C"]', '$[3]', 'G')

["A", "B", "C"]