如何在SQL中使用正则表达式提取JSON列数据至对应字段

正则表达式方案 #

不同数据库的正则函数语法略有差异，以下是针对主流数据库的实现：

MySQL

使用REGEXP_SUBSTR匹配并捕获目标值，再通过TRIM去除包裹的双引号：

SELECT 
  timestamp,
  name,
  TRIM('"' FROM REGEXP_SUBSTR(json_column, 'key1\s*:\s*"([^"]+)"', 1, 1, 'c', 1)) AS key1,
  TRIM('"' FROM REGEXP_SUBSTR(json_column, 'key2\s*:\s*"([^"]+)"', 1, 1, 'c', 1)) AS key2
FROM json_table;

正则说明：key1\s*:\s*"([^"]+)" 匹配key1及冒号前后的任意空格，捕获双引号内的非引号字符作为目标值。

PostgreSQL

可以用regexp_match或substring提取捕获组内容：

-- 方法1：regexp_match
SELECT 
  timestamp,
  name,
  (regexp_match(json_column, 'key1\s*:\s*"([^"]+)"'))[1] AS key1,
  (regexp_match(json_column, 'key2\s*:\s*"([^"]+)"'))[1] AS key2
FROM json_table;

-- 方法2：substring
SELECT 
  timestamp,
  name,
  substring(json_column FROM 'key1\s*:\s*"([^"]+)"') AS key1,
  substring(json_column FROM 'key2\s*:\s*"([^"]+)"') AS key2
FROM json_table;

SQL Server（2017+）

使用REGEXP_SUBSTRING提取捕获组，再去除双引号：

SELECT 
  timestamp,
  name,
  TRIM('"' FROM REGEXP_SUBSTRING(json_column, 'key1\s*:\s*"([^"]+)"', 1, 1, NULL, 1)) AS key1,
  TRIM('"' FROM REGEXP_SUBSTRING(json_column, 'key2\s*:\s*"([^"]+)"', 1, 1, NULL, 1)) AS key2
FROM json_table;

更可靠的原生JSON函数方案 #

正则处理JSON存在局限性（比如值包含转义双引号时会失效），推荐使用数据库原生的JSON解析函数，兼容性和稳定性更好：

MySQL

使用->>运算符直接提取字符串值：

SELECT 
  timestamp,
  name,
  json_column->>'$.key1' AS key1,
  json_column->>'$.key2' AS key2
FROM json_table;

PostgreSQL

先将字符串转为JSON类型，再用->>提取值：

SELECT 
  timestamp,
  name,
  json_column::json->>'key1' AS key1,
  json_column::json->>'key2' AS key2
FROM json_table;

SQL Server

使用JSON_VALUE函数提取指定路径的值：

SELECT 
  timestamp,
  name,
  JSON_VALUE(json_column, '$.key1') AS key1,
  JSON_VALUE(json_column, '$.key2') AS key2
FROM json_table;

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