如何在PostgreSQL中解析分号分隔的key:value键值对字符串

API 探路者

2026-10-2

PostgreSQL 分号分隔key:value字符串解析实现方案

核心思路

先将原始字符串按分号拆分得到单个key:value项，再按冒号拆分每项得到独立的键、值，可根据使用场景选择组装为键值映射结构或直接提取指定key的结果。

方法1：解析为hstore键值对（适合全量键值查询场景）

需要先开启hstore扩展，解析后可以直接通过key快速查询对应值：

开启hstore扩展（仅需执行一次）

CREATE EXTENSION IF NOT EXISTS hstore;

解析示例

SELECT 
  kv_map -> 'ref' AS ref_value,
  kv_map -> 'code' AS code_value
FROM (
  -- 组装键值对为hstore类型
  SELECT hstore(array_agg(split_part(kv_item, ':', 1)), array_agg(split_part(kv_item, ':', 2))) AS kv_map
  FROM (
    -- 按分号拆分字符串为单个key:value行
    SELECT unnest(string_to_array('ref:12345;code:ab', ';')) AS kv_item
  ) split_items
) parse_result;

方法2：无扩展直接提取指定key（适合少量key提取场景）

如果只需要提取个别key的值，无需开启额外扩展，直接用正则匹配即可：

-- 提取code对应的值
SELECT regexp_match('ref:12345;code:ab', 'code:([^;]+)')::text[] ->> 0 AS code_value;

-- 提取ref对应的值
SELECT regexp_match('ref:12345;code:ab', 'ref:([^;]+)')::text[] ->> 0 AS ref_value;

批量处理表数据示例

如果需要处理整张表的kv字段，假设表名为business_data，包含id、kv_str两个字段：

SELECT 
  id,
  regexp_match(kv_str, 'ref:([^;]+)')::text[] ->> 0 AS ref,
  regexp_match(kv_str, 'code:([^;]+)')::text[] ->> 0 AS code
FROM business_data;