PostgreSQL中更新JSON列数组对象指定值的原生SQL查询方法

核心方法 #

PostgreSQL中针对JSON类型列的数组元素字段更新，需借助jsonb_set函数实现（JSON类型需先转为JSONB处理，完成后可转回JSON），JSONB类型支持更灵活的嵌套结构修改操作。

具体更新语句 #

假设你的表名为your_table，JSON列名为your_json_column，要将数组第0个元素的reward字段更新为100（可自行替换目标值），执行以下SQL：

UPDATE your_table
SET your_json_column = (
  jsonb_set(
    your_json_column::jsonb,
    '{0, reward}',
    '100'::jsonb
  )::json
)
-- 务必添加WHERE条件指定目标行，避免全表更新
WHERE id = 1;

语句解析 #

• your_json_column::jsonb：将原JSON列转换为JSONB类型，适配jsonb_set函数的参数要求
• '{0, reward}'：JSON路径表达式，0代表数组的第0个元素，reward为要修改的字段名
• '100'::jsonb：要设置的新值，需转为JSONB格式（如果是字符串值，需写成'"your_string"'::jsonb）
• ::json：将处理后的JSONB转回JSON类型，匹配原列的类型（若原列是JSONB类型，可省略此转换）

验证更新结果 #

执行以下查询确认修改是否生效：

SELECT your_json_column->0->'reward' AS updated_reward
FROM your_table
WHERE id = 1;

注意事项 #

• 若列本身是JSONB类型，可直接简化语句，去掉两次类型转换：

  UPDATE your_table
  SET your_json_column = jsonb_set(your_json_column, '{0, reward}', '100'::jsonb)
  WHERE id = 1;
  

• 必须添加WHERE条件精准定位目标行，否则会批量更新全表数据

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