如何从PostgreSQL的text类型JSON数据列中删除指定对象

你之前操作失效的核心原因是jsondata列是text类型，无法直接使用PostgreSQL的JSON操作函数，需要先做类型转换再处理。

临时查询（仅查看删除b字段后的结果，不修改原数据） #

SELECT (jsondata::jsonb - 'b')::text AS jsondata_without_b 
FROM acqdata;

永久更新表数据 #

直接全表更新的语句：

UPDATE acqdata
SET jsondata = (jsondata::jsonb - 'b')::text;

如果部分行的jsondata内容不符合JSON格式，会导致更新报错，可以加过滤条件跳过非法数据：

-- PostgreSQL 16及以上版本可用
UPDATE acqdata
SET jsondata = (jsondata::jsonb - 'b')::text
WHERE jsondata IS NOT NULL 
  AND jsondata != ''
  AND jsonb_valid(jsondata);

-- 低于16的版本可用
UPDATE acqdata
SET jsondata = (jsondata::jsonb - 'b')::text
WHERE jsondata IS NOT NULL 
  AND jsondata != ''
  AND pg_input_is_valid(jsondata, 'jsonb');

长期优化建议 #

如果你后续需要频繁操作该列的JSON内容，建议直接将列类型修改为jsonb，后续JSON操作更简单、性能更高：

-- 修改列类型为jsonb
ALTER TABLE acqdata 
ALTER COLUMN jsondata TYPE jsonb 
USING jsondata::jsonb;

-- 修改后删除b字段的更新语句可以简化为
UPDATE acqdata SET jsondata = jsondata - 'b';

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