如何从存储在CLOB列中的JSON字符串提取name属性值

不同数据库的JSON操作内置函数存在差异，以下是主流数据库的可用写法：

• Oracle（12c及以上版本）
  直接使用内置的JSON_VALUE函数解析CLOB内容：

  SELECT JSON_VALUE(your_clob_column, '$.name') AS name
  FROM your_table_name;
  

  低版本不支持JSON函数的场景可以用正则匹配实现：

  SELECT REGEXP_SUBSTR(your_clob_column, '"name"\s*:\s*"([^"]+)"', 1, 1, NULL, 1) AS name
  FROM your_table_name;
  

• MySQL（5.7及以上版本）
  先将CLOB转为JSON类型后提取属性：

  -- 写法1：使用JSON_EXTRACT
  SELECT JSON_EXTRACT(CAST(your_clob_column AS JSON), '$.name') AS name FROM your_table_name;
  -- 写法2：使用->>简写直接返回不带引号的字符串结果
  SELECT CAST(your_clob_column AS JSON)->>'$.name' AS name FROM your_table_name;
  

• PostgreSQL
  强转CLOB为jsonb类型后取值：

  SELECT (your_clob_column::jsonb)->>'name' AS name
  FROM your_table_name;
  

• SQL Server（2016及以上版本）
  使用JSON_VALUE函数解析：

  SELECT JSON_VALUE(CAST(your_clob_column AS NVARCHAR(MAX)), '$.name') AS name
  FROM your_table_name;
  

注意：所有方案都要求存储的JSON字符串格式合法，若存在格式错误、转义异常的内容，函数会返回NULL或报错，可以搭配对应数据库的JSON合法性校验函数（如Oracle的JSON_IS_VALID、MySQL的JSON_VALID）过滤异常数据。

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