By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id INT, value JSON)
SELECT 1 id, '[{"id": 1, "value": 23.4}, {"id": 2, "value": 54.3}, {"id": 3, "value": 4.33}]' value;
SELECT id, JSON_PRETTY(value) FROM test;
Records: 1 Duplicates: 0 Warnings: 0
| id | JSON_PRETTY(value) |
|---|---|
| 1 | [ { "id": 1, "value": 23.4 }, { "id": 2, "value": 54.3 }, { "id": 3, "value": 4.33 } ] |
-- parse
SELECT id, rowid, JSON_PRETTY(object)
FROM test
CROSS JOIN JSON_TABLE( test.value,
'$[*]' COLUMNS (rowid FOR ORDINALITY,
object JSON PATH '$')) jsontable
| id | rowid | JSON_PRETTY(object) |
|---|---|---|
| 1 | 1 | { "id": 1, "value": 23.4 } |
| 1 | 2 | { "id": 2, "value": 54.3 } |
| 1 | 3 | { "id": 3, "value": 4.33 } |
-- parse and replace
SELECT id, JSON_PRETTY((JSON_REPLACE(object, '$.value', 0)))
FROM test
CROSS JOIN JSON_TABLE( test.value,
'$[*]' COLUMNS (rowid FOR ORDINALITY,
object JSON PATH '$')) jsontable
| id | JSON_PRETTY((JSON_REPLACE(object, '$.value', 0))) |
|---|---|
| 1 | { "id": 1, "value": 0 } |
| 1 | { "id": 2, "value": 0 } |
| 1 | { "id": 3, "value": 0 } |
-- parse, replace and reconstruct
SELECT id, JSON_PRETTY(JSON_ARRAYAGG(JSON_REPLACE(object, '$.value', 0)))
FROM test
CROSS JOIN JSON_TABLE( test.value,
'$[*]' COLUMNS (rowid FOR ORDINALITY,
object JSON PATH '$')) jsontable
GROUP BY id
| id | JSON_PRETTY(JSON_ARRAYAGG(JSON_REPLACE(object, '$.value', 0))) |
|---|---|
| 1 | [ { "id": 1, "value": 0 }, { "id": 2, "value": 0 }, { "id": 3, "value": 0 } ] |
-- update
UPDATE test
JOIN ( SELECT id, JSON_ARRAYAGG(JSON_REPLACE(object, '$.value', 0)) value
FROM test
CROSS JOIN JSON_TABLE( test.value,
'$[*]' COLUMNS (object JSON PATH '$')) jsontable
GROUP BY id ) data_for_update USING (id)
SET test.value = data_for_update.value;
SELECT id, JSON_PRETTY(value) FROM test;
Rows matched: 1 Changed: 1 Warnings: 0
| id | JSON_PRETTY(value) |
|---|---|
| 1 | [ { "id": 1, "value": 0 }, { "id": 2, "value": 0 }, { "id": 3, "value": 0 } ] |