MySQL8如何将数组追加到表JSON类型字段内部的嵌套数组中

原有语句问题说明 #

• 直接创建新数组覆盖了原有value字段的全部数据，丢失了name字段和原有host123记录
• 没有对@hostlist做JSON类型转换，直接存入会被识别为普通字符串而非数组
• 没有定位到嵌套的hosts数组路径，操作对象完全不符合预期
• 语法错误：UPDATE关键字后不需要加table，key是MySQL保留字需要加反引号包裹

正确更新语句 #

SET @hostlist = '["host343", "host345"]';

UPDATE t1
SET `value` = JSON_REPLACE(
    `value`,
    '$[0].hosts',
    JSON_MERGE_PRESERVE(
        JSON_EXTRACT(`value`, '$[0].hosts'),
        CAST(@hostlist AS JSON)
    )
)
WHERE `key` = 'key123';

逻辑说明 #

1. 用JSON_EXTRACT(value, '$[0].hosts')提取出原数据中第一个数组元素的hosts数组
2. 用CAST(@hostlist AS JSON)将字符串格式的主机列表转换为JSON数组类型
3. 用JSON_MERGE_PRESERVE合并原有hosts数组和新的主机数组
4. 用JSON_REPLACE将原位置的hosts数组替换为合并后的结果

执行后查询value字段即可得到预期结果：

[{"name": "name1", "hosts": ["host123","host343", "host345"]}]

内容的提问来源于stack exchange，提问作者meallhour