PostgreSQL中为JSONB列内所有对象增删color属性的UPDATE语句如何编写

正向迁移脚本（无color → 新增默认color属性） #

执行后将所有scene表squares字段数组内的每个对象添加"color": "#FFFFFF"属性：

UPDATE scene
SET squares = (
    SELECT jsonb_agg(elem || '{"color": "#FFFFFF"}'::jsonb ORDER BY ordinality)
    FROM jsonb_array_elements(squares) WITH ORDINALITY AS t(elem, ordinality)
)
WHERE squares IS NOT NULL AND jsonb_typeof(squares) = 'array';

回滚脚本（删除color属性） #

执行后将所有scene表squares字段数组内每个对象的color属性移除，回退到迁移前状态：

UPDATE scene
SET squares = (
    SELECT jsonb_agg(elem - 'color' ORDER BY ordinality)
    FROM jsonb_array_elements(squares) WITH ORDINALITY AS t(elem, ordinality)
)
WHERE squares IS NOT NULL AND jsonb_typeof(squares) = 'array';

说明 #

• 脚本中WITH ORDINALITY用于严格保证数组原有元素顺序不变，PostgreSQL 9.4及以上版本均支持该语法
• 末尾的WHERE条件过滤了非数组、空值的squares记录，避免无意义的更新和报错
• 正向脚本如果需要避免重复添加属性，可以额外在WHERE条件中追加AND NOT squares @> '[{"color": "#FFFFFF"}]'::jsonb，仅处理还没有对应属性的记录

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