如何在MySQL/MariaDB中插入不存在或部分存在的JSON路径

首先明确：MariaDB原生的JSON_INSERT、JSON_SET等函数不支持自动创建多级不存在的路径，它们要求路径的所有父节点必须已存在于JSON结构中，这就是你遇到多维路径操作失败的原因。

问题复现 #

你的示例数据：

jsondata = {"firstname":"Bob","stats":{"height":"72","weight":"200"}}

• 有效操作：父节点存在，直接插入

  UPDATE table SET jsondata = JSON_INSERT(jsondata, '$.lastname', 'Smith');
  UPDATE table SET jsondata = JSON_INSERT(jsondata, '$.stats.hair', 'Brown');
  

• 无效操作：父节点不存在，函数报错

  UPDATE table SET jsondata = JSON_INSERT(jsondata, '$.stats.hair.color', 'Brown'); -- $.stats.hair 不是对象
  UPDATE table SET jsondata = JSON_INSERT(jsondata, '$.car.make', 'Ford'); -- $.car 不存在
  

解决方案：自定义存储过程实现多级路径自动创建 #

可以编写一个存储过程，递归拆分路径层级，逐层检查并创建缺失的父节点，最后插入目标值。以下是一个可用的实现：

DELIMITER //

CREATE PROCEDURE JSON_INSERT_RECURSIVE(
    IN table_name VARCHAR(255),
    IN json_column VARCHAR(255),
    IN where_clause VARCHAR(1000), -- 用于定位要更新的行，比如 'id = 1'
    IN json_path VARCHAR(1000),
    IN json_value JSON
)
BEGIN
    DECLARE current_path VARCHAR(1000);
    DECLARE parent_path VARCHAR(1000);
    DECLARE path_parts JSON;
    DECLARE part_count INT;
    DECLARE i INT DEFAULT 1;
    DECLARE j INT;

    -- 拆分路径为层级数组（去掉开头的$）
    SET path_parts = JSON_ARRAY(TRIM(BOTH '.' FROM REPLACE(json_path, '$', '')));
    SET path_parts = JSON_REPLACE(path_parts, '$', REPLACE(JSON_EXTRACT(path_parts, '$'), '.', '","'));
    SET path_parts = JSON_CONCAT('["', JSON_UNQUOTE(JSON_EXTRACT(path_parts, '$')), '"]');
    SET part_count = JSON_LENGTH(path_parts);

    -- 逐层创建父节点
    WHILE i < part_count DO
        SET current_path = '$';
        SET j = 1;
        WHILE j <= i DO
            SET current_path = CONCAT(current_path, '.', JSON_UNQUOTE(JSON_EXTRACT(path_parts, CONCAT('$[', j-1, ']'))));
            SET j = j + 1;
        END WHILE;

        -- 检查当前路径是否存在，且是对象
        SET @check_sql = CONCAT(
            'SELECT JSON_TYPE(JSON_EXTRACT(', json_column, ', "', current_path, '")) INTO @path_type FROM ', table_name, ' WHERE ', where_clause, ' LIMIT 1'
        );
        PREPARE stmt FROM @check_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 如果路径不存在，创建空对象
        IF @path_type IS NULL THEN
            SET @update_sql = CONCAT(
                'UPDATE ', table_name, ' SET ', json_column, ' = JSON_INSERT(', json_column, ', "', current_path, '", JSON_OBJECT()) WHERE ', where_clause
            );
            PREPARE stmt FROM @update_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        SET i = i + 1;
    END WHILE;

    -- 插入最终值
    SET @final_sql = CONCAT(
        'UPDATE ', table_name, ' SET ', json_column, ' = JSON_INSERT(', json_column, ', "', json_path, '", ', JSON_UNQUOTE(json_value), ') WHERE ', where_clause
    );
    PREPARE stmt FROM @final_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

使用方法 #

比如要插入$.stats.hair.color = 'Brown'，执行：

CALL JSON_INSERT_RECURSIVE('your_table_name', 'jsondata', 'id = 1', '$.stats.hair.color', '"Brown"');

要插入$.car.make = 'Ford'，执行：

CALL JSON_INSERT_RECURSIVE('your_table_name', 'jsondata', 'id = 1', '$.car.make', '"Ford"');

注意事项 #

1. where_clause要确保能唯一定位行，避免批量更新出错；
2. 若JSON列是LONGTEXT类型，MariaDB会自动兼容为JSON处理；
3. 可修改存储过程中的JSON_INSERT为JSON_SET，实现覆盖已存在值的逻辑。

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