PostgreSQL 14中如何将JSON字符串转换为表格形式？

你可以通过PostgreSQL的JSON处理函数和字符串操作函数组合实现需求，具体步骤如下：

核心SQL查询 #

假设你的JSON字符串是已知输入（或存储在表的列中），可以使用以下查询直接生成目标表格：

WITH input_json AS (
    -- 替换为你的JSON字符串或表列引用
    SELECT '{"tc_0": "Namefield1:ValueField", "tc_2": "Namefield2:", "tc_1": "Namefield3:", "tc_3": "Namefield4:ValueField:ValueField"}'::jsonb AS js
)
SELECT 
    split_part(val, ':', 1) AS name,
    CASE 
        -- 处理末尾带冒号的情况，返回null
        WHEN strpos(val, ':') = length(val) THEN NULL
        -- 提取冒号后的全部内容（含多个冒号的场景）
        ELSE substring(val from strpos(val, ':') + 1)
    END AS value
FROM input_json,
     -- 展开JSON为键值对行
     jsonb_each_text(js) AS kv(key, val)
-- 按tc_后的数字升序排序，匹配示例输出顺序
ORDER BY (substring(kv.key from 'tc_(\d+)')::integer);

关键函数说明 #

• jsonb_each_text(js)：将JSON对象拆分为多行的键（key）值（val）对，是处理JSON对象的基础函数。
• substring(kv.key from 'tc_(\d+)')::integer：从键名中提取tc_后的数字并转为整数，用于排序保证输出顺序与tc_0、tc_1、tc_2、tc_3的顺序一致。
• split_part(val, ':', 1)：截取第一个冒号左侧的内容作为name字段。
• CASE语句：判断值字符串的结构，若冒号在末尾则返回null，否则提取冒号后的全部内容（支持包含多个冒号的场景）。

适配表列场景 #

如果JSON数据存储在表的列中（例如表名为json_data，列名为raw_json），可以简化查询：

SELECT 
    split_part(val, ':', 1) AS name,
    CASE 
        WHEN strpos(val, ':') = length(val) THEN NULL
        ELSE substring(val from strpos(val, ':') + 1)
    END AS value
FROM json_data,
     jsonb_each_text(raw_json) AS kv(key, val)
ORDER BY (substring(kv.key from 'tc_(\d+)')::integer);

执行上述查询后，将直接得到你需要的表格结果，与示例中的values子查询输出完全一致。

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