如何将两种指定格式字符串转换为Postgresql的json[]类型列

转换逻辑说明 #

两种输入格式的核心差异为外层包裹符号和引号类型，可通过统一预处理消除差异后完成类型转换：

• 第一步：将所有单引号替换为JSON标准要求的双引号
• 第二步：将第一种格式外层的{}替换为JSON数组标准的[]
• 第三步：将预处理后的标准JSON数组字符串转换为PostgreSQL的json[]类型

可直接执行的SQL示例 #

假设你的表名为业务表名，存储原始文本的列名为原始文本列名，目标json[]类型列名为目标列名，可直接用如下语句完成更新：

UPDATE 业务表名
SET 目标列名 = array(
    SELECT json_array_elements(
        regexp_replace(
            regexp_replace(
                replace(原始文本列名, '''', '"'),
                '^{', '['
            ),
            '}$', ']'
        )::json
    )
);

效果验证 #

可先执行查询验证转换结果是否符合预期：

SELECT 
    原始文本列名,
    array(
        SELECT json_array_elements(
            regexp_replace(
                regexp_replace(
                    replace(原始文本列名, '''', '"'),
                    '^{', '['
                ),
                '}$', ']'
            )::json
        )
    ) AS 转换后json数组
FROM 业务表名
LIMIT 10;

异常场景适配 #

如果原始文本中存在内容本身带单引号的情况，可在替换单引号前先对内容内的单引号做转义处理，根据实际格式调整替换规则即可。

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