PostgreSQL迁移JSONB列：嵌套对象映射转简单键值对实现

需求说明 #

Flyway数据迁移任务中，需对PostgreSQL的jsonb类型列做结构转换：

• 原始数据为嵌套对象结构，每个一级属性对应的值是包含type、value字段的对象：

{
  "property1": 
  {
    "type": "A",
    "value": "value1"
  },
  "property2": 
  {
    "type": "B",
    "value": "value2"
  }
}

• 目标结构为单层键值对，仅保留一级属性名、以及对应嵌套对象中value字段的实际值：

{
  "property1": "value1",
  "property2": "value2"
}

原有实现硬编码了固定值dummyValue作为所有键的对应值，无法动态提取嵌套对象内的目标字段。

可直接使用的更新SQL #

核心逻辑是先拆分原始jsonb的一级键值对，提取目标字段后重新聚合为新的jsonb对象：

UPDATE sms_notification n
SET param2 = (
  SELECT jsonb_object_agg(kv.key, kv.value_obj -> 'value')
  FROM jsonb_each(n.params) AS kv(key, value_obj)
);

逻辑拆解 #

• jsonb_each(n.params)：将params列的一级键值对拆分为多行结果，每行包含两个字段：key为一级属性名（如property1），value_obj为该属性对应的嵌套对象
• kv.value_obj -> 'value'：从嵌套对象中提取value字段的存储值
• jsonb_object_agg：将处理后的键、目标值重新聚合为标准单层jsonb对象

上线前校验方法 #

正式执行更新前，先执行查询预览转换结果，确认符合预期后再跑更新语句：

SELECT
  params AS original_structure,
  (
    SELECT jsonb_object_agg(kv.key, kv.value_obj -> 'value')
    FROM jsonb_each(n.params) AS kv(key, value_obj)
  ) AS converted_structure
FROM sms_notification n
LIMIT 20;

异常数据处理：如果存在部分嵌套对象缺失value字段的情况，转换后对应键的值会为null，需要过滤这类键的话，可在子查询中增加WHERE kv.value_obj ? 'value'条件。

内容的提问来源于stack exchange，提问作者Tomáš Mika