无法将数据库表列内JSON数据按指定格式更新问题求助

核心逻辑 #

你需要将JSON中salesValuesOption字段的品牌-销售额键值对结构，转换为optionN存品牌、valueN存对应销售额的结构，整体处理流程如下：

• 读取原JSON字段，解析为结构化对象
• 遍历salesValuesOption下的所有键值对，按顺序生成新的键值结构
• 替换原字段后将新JSON回写入数据库对应列

推荐实现方案 #

方案1：Python脚本批量处理（全数据库通用，灵活性最高） #

适配所有支持JSON存储的数据库，不管键数是否固定都可以用，操作前记得先备份全表数据：

import json
# 导入对应数据库的驱动，MySQL用pymysql，PostgreSQL用psycopg2，SQLite用自带sqlite3
import pymysql

# 建立数据库连接
db_conn = pymysql.connect(
    host="你的数据库地址",
    user="数据库用户名",
    password="数据库密码",
    database="对应库名",
    charset="utf8mb4"
)
cursor = db_conn.cursor()

# 假设你的表名为user_shops，存储JSON的列名为ext_info，主键为id
cursor.execute("SELECT id, ext_info FROM user_shops")
all_records = cursor.fetchall()

for record in all_records:
    row_id, origin_json_str = record
    # 解析原JSON
    origin_data = json.loads(origin_json_str)
    old_sales_map = origin_data.get("salesValuesOption", {})
    # 生成新的salesValuesOption结构
    new_sales_map = {}
    for idx, (brand_name, sales_val) in enumerate(old_sales_map.items(), start=1):
        new_sales_map[f"option{idx}"] = brand_name
        new_sales_map[f"value{idx}"] = sales_val
    # 替换原字段
    origin_data["salesValuesOption"] = new_sales_map
    # 转成JSON字符串写回数据库
    new_json_str = json.dumps(origin_data, ensure_ascii=False)
    cursor.execute(
        "UPDATE user_shops SET ext_info = %s WHERE id = %s",
        (new_json_str, row_id)
    )
    db_conn.commit()

# 关闭连接
cursor.close()
db_conn.close()

注意：你示例的目标JSON里存在value1重复、Option4首字母大写的问题，属于不规范的JSON结构，上面的脚本生成的是统一小写、键名不重复的标准结构，如果你确实需要和示例完全对齐，自行修改遍历逻辑即可。

方案2：MySQL 8.0+/MariaDB 10.2+ 直接SQL更新 #

如果你的数据库支持JSON操作函数，且salesValuesOption下固定只有5个键，可以直接用SQL操作，不用写代码：

-- 先备份整表，避免误操作
CREATE TABLE user_shops_bak AS SELECT * FROM user_shops;

-- 执行更新
UPDATE user_shops
SET ext_info = JSON_REPLACE(
    ext_info,
    '$.salesValuesOption',
    JSON_OBJECT(
        'option1', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[0]),
        'value1', JSON_UNQUOTE(JSON_EXTRACT(ext_info->'$.salesValuesOption', CONCAT('$.', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[0])))),
        'option2', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[1]),
        'value2', JSON_UNQUOTE(JSON_EXTRACT(ext_info->'$.salesValuesOption', CONCAT('$.', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[1])))),
        'option3', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[2]),
        'value3', JSON_UNQUOTE(JSON_EXTRACT(ext_info->'$.salesValuesOption', CONCAT('$.', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[2])))),
        'option4', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[3]),
        'value4', JSON_UNQUOTE(JSON_EXTRACT(ext_info->'$.salesValuesOption', CONCAT('$.', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[3])))),
        'option5', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[4]),
        'value5', JSON_UNQUOTE(JSON_EXTRACT(ext_info->'$.salesValuesOption', CONCAT('$.', JSON_UNQUOTE(JSON_KEYS(ext_info->'$.salesValuesOption')[4]))))
    )
)
WHERE JSON_VALID(ext_info) = 1 AND ext_info->'$.salesValuesOption' IS NOT NULL;

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