PostgreSQL 9.6如何从文本字段提取指定键值对？

你的arguments字段存储的是PHP风格的数组字符串，并非PostgreSQL原生数组类型，因此需要通过文本解析或格式转换来提取指定键值。以下是几种可行方案：

方案一：正则表达式直接提取（适合简单固定结构） #

利用PostgreSQL的substring函数配合正则表达式，直接匹配目标键对应的值：

SELECT
  substring(arguments FROM 'state'' => ''([^'']+)''') AS state,
  substring(arguments FROM 'zip'' => ''([^'']+)''') AS zip
FROM table_name;

说明 #

正则表达式state'' => ''([^'']+)''会精准匹配state' => '的格式，捕获后续直到下一个单引号的内容，也就是对应的值。该方法简单高效，但仅适用于字段格式固定、无嵌套变化的场景。

方案二：转换为JSON后提取（更可靠，支持嵌套结构） #

先将PHP数组格式的字符串转换为JSON格式，再利用PostgreSQL的JSON函数提取嵌套值，适配更复杂的结构：

SELECT
  json_extract_path_text(
    -- 逐步将PHP数组转换为JSON格式
    replace(
      replace(
        replace(
          regexp_replace(arguments, '\s+array\s+\(', '{', 'g'),
          '=>', ':'
        ),
        '''', '"'
      ),
      ',\s*}', '}'  -- 移除数组末尾多余的逗号
    )::json,
    'description', 'state'
  ) AS state,
  json_extract_path_text(
    replace(
      replace(
        replace(
          regexp_replace(arguments, '\s+array\s+\(', '{', 'g'),
          '=>', ':'
        ),
        '''', '"'
      ),
      ',\s*}', '}'
    )::json,
    'description', 'zip'
  ) AS zip
FROM table_name;

转换逻辑 #

1. 用regexp_replace把array (替换为JSON的{
2. 把PHP的=>替换为JSON的:
3. 把单引号'替换为JSON要求的双引号"
4. 移除数组末尾多余的逗号（避免JSON解析报错）

转换完成后，通过json_extract_path_text逐层提取description下的state和zip值。

方案三：使用PL/PHP函数（需扩展支持） #

如果你的PostgreSQL安装了plphp扩展，可以直接解析PHP数组字符串，这种方式最贴合原始格式，但需注意安全风险：

-- 创建解析函数
CREATE OR REPLACE FUNCTION parse_php_array(text) RETURNS json AS $$
  $arr = eval('return ' . $args[0] . ';');
  return json_encode($arr);
$$ LANGUAGE plphp;

-- 提取目标值
SELECT
  json_extract_path_text(parse_php_array(arguments)::json, 'description', 'state') AS state,
  json_extract_path_text(parse_php_array(arguments)::json, 'description', 'zip') AS zip
FROM table_name;

注意 #

eval函数会执行传入的PHP代码，如果arguments字段可能被注入恶意内容，请勿使用此方案。

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