By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data (id int, j json)
INSERT INTO data values (2, '["new", "orange"]'), (1, '["hello", "world"]');
Records: 2 Duplicates: 0 Warnings: 0
SELECT *
FROM data
| id | j |
|---|---|
| 2 | 5b226e6577222c20226f72616e6765225d |
| 1 | 5b2268656c6c6f222c2022776f726c64225d |
SELECT JSON_SEARCH(j, 'one', 'orange')
FROM data
| JSON_SEARCH(j, 'one', 'orange') |
|---|
| "$[1]" |
| null |
UPDATE data
SET j = JSON_REMOVE(j, JSON_UNQUOTE(JSON_SEARCH(j, 'one', 'orange')))
WHERE JSON_SEARCH(j, 'one', 'orange') IS NOT NULL
Rows matched: 1 Changed: 1 Warnings: 0
SELECT *
FROM data
| id | j |
|---|---|
| 2 | 5b226e6577225d |
| 1 | 5b2268656c6c6f222c2022776f726c64225d |