PostgreSQL中如何将特殊格式字符串转换为可用JSON？

问题根源 #

你遇到的两个问题本质是字符串格式错误和转换方法误用：

• 第一个字符串里的"S"未做转义，属于非法JSON格式，直接用::json转换必然报错；
• to_json()的作用是把任意PostgreSQL类型转成JSON类型，会把目标字符串包装成JSON的字符串值（外层多一层引号），自然没法用->提取内部键值。

具体解决步骤 #

处理带未转义引号的非法字符串 #

先把字符串里的裸双引号转义成\"，再转换为JSON类型：

-- 单个替换（针对"xtz "S""这类特定情况）
SELECT replace(your_column, '"S"', '\\"S"')::json FROM your_table;

如果存在多处裸双引号，用正则批量替换更高效：

-- 批量转义所有未正确转义的内部双引号
SELECT regexp_replace(your_column, '(?<!\\)"(?!(?:[^"]*"[^"]*")*[^"]*$)', '\\"', 'g')::json FROM your_table;

处理格式合法的字符串 #

直接用::json或cast()转换即可，不要用to_json()：

SELECT your_column::json FROM your_table;

统一处理两种情况 #

如果表中同时混着合法和非法的字符串，用CASE语句自动判断处理：

SELECT 
  CASE 
    -- 检测是否存在未转义的内部双引号
    WHEN your_column ~ '(?<!\\)"(?!(?:[^"]*"[^"]*")*[^"]*$)' 
    THEN regexp_replace(your_column, '(?<!\\)"(?!(?:[^"]*"[^"]*")*[^"]*$)', '\\"', 'g')::json
    ELSE your_column::json
  END AS json_col
FROM your_table;

验证转换效果 #

转换完成后，就能正常提取键值：

SELECT 
  json_col -> 0 ->> 'name' AS 名称,
  json_col -> 0 ->> 'strength' AS 规格
FROM (
  SELECT your_column::json AS json_col FROM your_table WHERE your_column = '[{"name": "xtz ", "strength": "25 x/1"}]'
) t;

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