我正在尝试更新一个mysql JSON字段，该字段有一个JSON OBJECTS数组。
尝试使用以下查询
select JSON_REPLACE('{"k1":[{"id":1,"name":"guna","age":34},{"id":2,"name":"guna1","age":34},{"id":3,"name":"guna2","age":34}]}','$.k1[0]','{"id":1,"name":"guna","age":34,"hi":"hi"}');
更新了错误的JSON值。
更新JSON值后，列值在每个("(引号之前都有((斜杠。在更新JSON列时，不应添加斜线。
SET @json := '{"k1":[{"id":1,"name":"guna","age":34},
{"id":2,"name":"guna1","age":34},
{"id":3,"name":"guna2","age":34}]}';
SET @replacement := '{"id":1,"name":"guna","age":34,"hi":"hi"}';
SELECT JSON_OBJECT('k1', JSON_ARRAYAGG(CAST(object AS JSON)))
FROM ( SELECT COALESCE(replacement, object) object
FROM JSON_TABLE(@json,
'$.k1[*]' COLUMNS (rowid FOR ORDINALITY,
object JSON PATH '$')) jsontable
LEFT JOIN (SELECT @replacement replacement) replacement
ON object->'$.id' = replacement->'$.id'
ORDER BY rowid ASC LIMIT 2147483647) reconstruction;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4b126516d8dd5a5ea151ed3058b037c6
更新JSON值后，列值在每个(quot；(引号之前都有((斜杠。
用于更新的值被视为字符串值，并且所有"都被引用。必须将该值显式转换为JSON数据类型。
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1ad51f6f1df4a2c1dfdd6daa25ddcb88