如何在PostgreSQL中将类字典字符串转换为结构化表格？

问题背景 #

输入类字典格式字符串：
{100: {1:500000, 2: 600000}, 200: {1:700000, 2: 800000}, 300: {1:900000, 2: 1000000}}
层级结构固定（外层键对应内层字典），但键值对数量不固定，需要转换为如下结构化表格：

尝试用regexp_split_to_table(string, '[{}:, ]+')拆分得到单行数字，但无法重组为目标表格，求可行方案。

方法一：转换为JSONB递归解析（推荐） #

PostgreSQL的JSONB类型能高效处理嵌套结构，先把类字典字符串转成合法JSON，再递归展开：

1. 修复JSON格式：
   原字符串的数字键不符合JSON规范（JSON键必须是字符串），用正则把所有数字:替换为"数字":：

   SELECT regexp_replace(
     '{100: {1:500000, 2: 600000}, 200: {1:700000, 2: 800000}, 300: {1:900000, 2: 1000000}}',
     '(\d+):',
     '"\1":',
     'g'
   ) AS valid_json;
   

2. 递归展开并提取数据：
   把修复后的字符串转成JSONB，通过jsonb_each依次展开外层和内层键值对，最终得到目标表格：

   WITH json_source AS (
     SELECT jsonb(regexp_replace(
       '{100: {1:500000, 2: 600000}, 200: {1:700000, 2: 800000}, 300: {1:900000, 2: 1000000}}',
       '(\d+):',
       '"\1":',
       'g'
     )) AS nested_data
   )
   SELECT 
     outer.key::INT AS "column 1",
     inner.key::INT AS "column 2",
     inner.value::BIGINT AS "column 3"
   FROM json_source,
        jsonb_each(nested_data) AS outer,
        jsonb_each_text(outer.value) AS inner;
   

这种方法容错性强，即使原字符串有多余空格或键值对数量变化，也能正确解析。

方法二：基于正则拆分的分组处理 #

如果坚持用regexp_split_to_table拆分，可通过窗口函数对拆分后的数字按每3个一组关联：

WITH split_numbers AS (
  SELECT 
    trim(num_str) AS num,
    row_number() OVER () AS row_idx
  FROM regexp_split_to_table(
    '{100: {1:500000, 2: 600000}, 200: {1:700000, 2: 800000}, 300: {1:900000, 2: 1000000}}',
    '[{}:, ]+'
  ) AS num_str
  WHERE trim(num_str) <> '' -- 过滤拆分产生的空字符串
)
SELECT
  s1.num::INT AS "column 1",
  s2.num::INT AS "column 2",
  s3.num::BIGINT AS "column 3"
FROM split_numbers s1
JOIN split_numbers s2 ON s2.row_idx = s1.row_idx + 1
JOIN split_numbers s3 ON s3.row_idx = s1.row_idx + 2
WHERE s1.row_idx % 3 = 1; -- 只取每组的起始行作为关联基准

注意：这种方法依赖拆分后数字的顺序严格是外层键→内层键→值的循环，若原字符串格式有变动（比如值包含特殊字符），可能导致解析错误，仅适合格式完全固定的场景。

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