Postgres 9.5迁移脚本需求：为jsonb字段添加含true值键的opts数组

问题描述 #

现有questions表，包含settings jsonb列，初始数据如下：

{ "id": "question-id-1", "settings": { "foo1": true, "foo2": true, "bar": false } },
{ "id": "question-id-2", "settings": { "bar": true } }

需要编写PostgreSQL 9.5兼容的更新迁移脚本，将数据更新为：

{ "id": "question-id-1", "settings": { "foo1": true, "foo2": true, "bar": false, "opts": ["foo1", "foo2"] } },
{ "id": "question-id-2", "settings": { "bar": true, "opts": ["bar"] } }

核心要求：仅将settings中值为true的键名添加至新增的opts数组中。

解决方案 #

以下是完全适配PostgreSQL 9.5的更新脚本：

UPDATE questions
SET settings = jsonb_set(
    settings,
    '{opts}',
    (
        SELECT jsonb_agg(key)
        FROM jsonb_each_text(settings)
        WHERE value = 'true'
    ),
    true -- 若opts键已存在则覆盖，不存在则新增
);

脚本解释 #

• jsonb_each_text(settings)：把settings这个JSONB对象拆解成多行键值对记录，其中key是原对象的键名，value是对应值的文本形式（JSON布尔值true会转为字符串'true'）。
• WHERE value = 'true'：过滤出所有值为true的键，只保留这些键名用于后续数组生成。
• jsonb_agg(key)：将筛选后的键名聚合为一个JSONB数组，这就是我们需要的opts数组内容。
• jsonb_set(...)：把生成的数组插入到settings对象的opts路径下。最后一个参数true确保无论opts键是否已存在，都能正确更新或新增。

额外说明 #

• 如果某条记录的settings中没有值为true的键，脚本会自动给opts赋值为空数组[]，逻辑上符合预期。
• 所有用到的函数（jsonb_each_text、jsonb_agg、jsonb_set）都是PostgreSQL 9.5原生支持的，无需额外扩展。

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