PostgreSQL单查询批量修改指定JSONB对象的color与title

需求说明 #

用单条UPDATE语句，修改JSON字段中items对象内指定键（0024dd81-db96-4a87-863b-e6ca9dd69d91、0024dd81-db96-4a87-863b-e6ca9dd69d92、0024dd81-db96-4a87-863b-e6ca9dd69d93）对应的子对象的color和title为统一值，其他键的属性保持不变。

修改前数据示例 #

{
  "designation": "test",
  "items": {
    "0024dd81-db96-4a87-863b-e6ca9dd69d90": {
      "id": 71,
      "color": "#FFFFFF",
      "title": "item1"
    }, 
    "0024dd81-db96-4a87-863b-e6ca9dd69d91": {
      "id": 72,
      "color": "#FFFFFF",
      "title": "item2"
    },
    "0024dd81-db96-4a87-863b-e6ca9dd69d92": {
      "id": 73,
      "color": "#FFFFFF",
      "title": "item3"
    },
    "0024dd81-db96-4a87-863b-e6ca9dd69d93": {
      "id": 74,
      "color": "#FFFFFF",
      "title": "item4"
    }
  }
}

修改后数据示例 #

{
  "designation": "test",
  "items": {
    "0024dd81-db96-4a87-863b-e6ca9dd69d90": {
      "id": 71,
      "color": "#FFFFFF",
      "title": "item1"
    }, 
    "0024dd81-db96-4a87-863b-e6ca9dd69d91": {
      "id": 72,
      "color": "#FFFFFF",
      "title": "updated"
    },
    "0024dd81-db96-4a87-863b-e6ca9dd69d92": {
      "id": 73,
      "color": "#FFFFFF",
      "title": "updated"
    },
    "0024dd81-db96-4a87-863b-e6ca9dd69d93": {
      "id": 74,
      "color": "#FFFFFF",
      "title": "updated"
    }
  }
}

解决方案 #

假设你的表名为target_table，存储JSON数据的字段名为json_data（推荐用jsonb类型，修改效率比json更高），执行以下单条UPDATE语句即可实现需求：

UPDATE target_table
SET json_data = jsonb_set(
    json_data,
    '{items}',
    (
        SELECT jsonb_object_agg(
            key,
            CASE WHEN key IN (
                '0024dd81-db96-4a87-863b-e6ca9dd69d91',
                '0024dd81-db96-4a87-863b-e6ca9dd69d92',
                '0024dd81-db96-4a87-863b-e6ca9dd69d93'
            ) THEN value || '{"color": "#FFFFFF", "title": "updated"}'::jsonb
                 ELSE value
            END
        )
        FROM jsonb_each(json_data->'items')
    )
)
WHERE json_data->'items' ?| ARRAY[
    '0024dd81-db96-4a87-863b-e6ca9dd69d91',
    '0024dd81-db96-4a87-863b-e6ca9dd69d92',
    '0024dd81-db96-4a87-863b-e6ca9dd69d93'
];

语句解释 #

1. jsonb_each(json_data->'items')：把items对象拆分成键值对的行数据，方便逐个处理每个子对象。
2. CASE条件判断：检查当前键是否在目标列表中，若是则用||运算符将原对象与新的color、title合并（新属性会覆盖原对象中的同名属性）；否则保留原对象。
3. jsonb_object_agg(key, value)：将处理后的键值对重新组装成完整的items对象。
4. jsonb_set(json_data, '{items}', ...)：把原JSON字段中的items部分替换为新组装的对象。
5. WHERE子句：用?|运算符过滤出至少包含一个目标键的记录，避免对无匹配键的行做无意义更新。

注意事项 #

• 如果你的JSON字段是json类型，需要先转成jsonb处理，例如将json_data替换为json_data::jsonb，修改后可再转回json类型（但建议直接使用jsonb提升性能）。
• 若要修改的color或title是动态值，可把字符串替换为变量或参数。

内容的提问来源于stack exchange，提问作者R00t Killer