如何将逗号分隔的非JSON字符串转换为指定格式JSON？

从表tt_d_tab的col1字段中取出格式为'A:10000000,B:50000000,C:1000000,D:10000000,E:10000000'的字符串，需要将其转换为标准JSON格式：'{"A": 10000000,"B": 50000000,"C": 1000000,"D": 10000000,"E": 10000000}'

MySQL 解决方案 #

直接字符串构造法 #

通过嵌套替换生成JSON格式字符串，再转为JSON类型：

SELECT 
  CAST(
    CONCAT('{', REPLACE(REPLACE(col1, ':', '": '), ',', ',"'), '}') 
    AS JSON
  ) AS json_result
FROM tt_d_tab;

步骤说明：

• 将X:Y替换为X": Y，给键名添加双引号
• 将逗号,替换为,"，实现键值对的合法分隔
• 前后拼接{}形成完整JSON结构，用CAST转为JSON类型确保合法性

拆分聚合法（严谨版） #

适合需要严格校验键值对格式的场景：

SELECT 
  JSON_OBJECTAGG(split_kv.key, split_kv.value) AS json_result
FROM tt_d_tab,
     JSON_TABLE(
       CONCAT('["', REPLACE(col1, ',', '","'), '"]'),
       '$[*]' COLUMNS (
         kv VARCHAR(255) PATH '$'
       )
     ) AS kv_list,
     JSON_TABLE(
       CONCAT('["', REPLACE(kv_list.kv, ':', '","'), '"]'),
       '$[*]' COLUMNS (
         key VARCHAR(255) PATH '$[0]',
         value BIGINT PATH '$[1]'
       )
     ) AS split_kv
GROUP BY tt_d_tab.col1;

PostgreSQL 解决方案 #

简洁字符串转换 #

利用PostgreSQL的类型转换直接生成JSON：

SELECT 
  ('{' || replace(replace(col1, ':', '": '), ',', ',"') || '}')::jsonb AS json_result
FROM tt_d_tab;

使用jsonb类型可自动验证JSON合法性，比json类型更严谨。

拆分聚合法 #

确保键值类型正确，适合复杂场景：

SELECT 
  jsonb_object_agg(
    split_part(kv, ':', 1), 
    split_part(kv, ':', 2)::bigint
  ) AS json_result
FROM tt_d_tab,
     unnest(string_to_array(col1, ',')) AS kv;

步骤说明：

• string_to_array按逗号拆分原始字符串为数组
• unnest将数组转为行数据
• split_part拆分每个键值对为键和数值（转为bigint确保数字类型）
• jsonb_object_agg聚合键值对为JSON对象

Oracle 12c+ 解决方案 #

正则替换法 #

通过正则表达式快速生成JSON：

SELECT 
  REGEXP_REPLACE(
    REGEXP_REPLACE(col1, '([^:,]+):([^,]+)', '"\1": \2'),
    '^', '{') || '}' AS json_result
FROM tt_d_tab;

步骤说明：

• 内层正则将X:Y替换为"X": Y
• 外层正则在开头添加{，末尾拼接}形成完整JSON结构

拆分聚合法 #

适合需要严格类型处理的场景：

SELECT 
  JSON_OBJECTAGG(key, value RETURNING CLOB) AS json_result
FROM (
  SELECT 
    REGEXP_SUBSTR(kv, '[^:]+', 1, 1) AS key,
    TO_NUMBER(REGEXP_SUBSTR(kv, '[^:]+', 1, 2)) AS value
  FROM tt_d_tab,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL 
             FROM DUAL 
             CONNECT BY REGEXP_SUBSTR(col1, '[^,]+', 1, LEVEL) IS NOT NULL
           ) AS SYS.ODCINUMBERLIST
         )
       )
  CROSS APPLY (
    SELECT REGEXP_SUBSTR(col1, '[^,]+', 1, COLUMN_VALUE) AS kv
  )
)
GROUP BY col1;

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