PostgreSQL：将varchar集合转换为指定结构JSON的方法

要实现将格式为{item1,item2,...}的varchar列转换为指定结构的JSON数组，可以通过字符串处理+JSON聚合函数的组合完成，以下是具体操作：

核心转换逻辑 #

针对单条数据的转换语句示例：

SELECT 
  CASE 
    WHEN items IS NOT NULL THEN
      json_agg(
        json_build_object('name', 'SOME_NAME', 'item', unnest_item)
      )
    ELSE NULL
  END AS new_items
FROM (
  SELECT unnest(string_to_array(regexp_replace(items, '^\{|\}$', '', 'g'), ',')) AS unnest_item
  FROM your_table
  WHERE items IS NOT NULL
) sub;

逻辑拆解 #

• 清理字符串：regexp_replace(items, '^\{|\}$', '', 'g')去掉字符串首尾的{}，将{item1,item2}转为item1,item2。
• 分割为数组：string_to_array(..., ',')把清理后的字符串按逗号分割成文本数组。
• 展开数组元素：unnest(...)将数组拆分为单行的单个item值。
• 生成单个JSON对象：json_build_object('name', 'SOME_NAME', 'item', unnest_item)为每个item生成指定结构的JSON对象。
• 聚合成JSON数组：json_agg(...)把所有单个JSON对象聚合为一个JSON数组。
• 保留NULL值：通过CASE语句维持原始的NULL，避免转换为空数组。

完整操作流程 #

1. 新增JSON类型列（可选，避免直接修改原列） #

ALTER TABLE your_table ADD COLUMN new_items JSON;

2. 更新数据到新列 #

UPDATE your_table
SET new_items = (
  SELECT json_agg(json_build_object('name', 'SOME_NAME', 'item', unnest_item))
  FROM (
    SELECT unnest(string_to_array(regexp_replace(items, '^\{|\}$', '', 'g'), ',')) AS unnest_item
    WHERE items IS NOT NULL
  ) sub
)
WHERE items IS NOT NULL;

注：WHERE子句可跳过NULL值的无效更新，提升执行效率

3. 验证转换结果 #

SELECT items, new_items FROM your_table;

输出结果与预期一致：

+-----------------+----------------------------------------------------------------------------------+
| items (varchar) |                               new_items (json)                                   |
+-----------------+----------------------------------------------------------------------------------+
| {item1,item2}   | [{"name": "SOME_NAME", "item": "item1"}, {"name": "SOME_NAME", "item": "item2"}] |
| null            | null                                                                             |
| {item3,item4}   | [{"name": "SOME_NAME", "item": "item3"}, {"name": "SOME_NAME", "item": "item4"}] |
+-----------------+----------------------------------------------------------------------------------+

4. （可选）替换原列类型 #

确认转换正确后，可将原列直接改为JSON类型：

-- 更新原列数据为转换后的JSON格式
UPDATE your_table
SET items = (
  SELECT json_agg(json_build_object('name', 'SOME_NAME', 'item', unnest_item))
  FROM (
    SELECT unnest(string_to_array(regexp_replace(items, '^\{|\}$', '', 'g'), ',')) AS unnest_item
    WHERE items IS NOT NULL
  ) sub
)
WHERE items IS NOT NULL;

-- 修改列类型
ALTER TABLE your_table ALTER COLUMN items TYPE JSON USING items::JSON;

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