Oracle中使用REGEXP_SUBSTR提取CLOB内指定JSON字段值

嘿，这个需求我太熟悉了！要从存储JSON的CLOB列里提取"nomeUsuario"对应的用户名，其实可以根据你用的Oracle版本来选最适合的方法，毕竟不同版本对JSON的支持力度不一样：

方法一：用Oracle 12c+原生JSON函数（推荐） #

如果你的Oracle数据库是12c及以上版本，直接用原生的JSON_VALUE函数就好，这是最稳妥的方式，完全不用担心JSON里的空格、换行或者格式小变动。语句如下：

SELECT 
  JSON_VALUE(your_clob_column FORMAT JSON, '$.nomeUsuario') AS nome_usuario
FROM 
  your_table;

• FORMAT JSON：告诉Oracle这个CLOB字段里存的是JSON格式数据
• $.nomeUsuario：JSON路径表达式，直接定位到我们要的"nomeUsuario"字段值

举个具体的例子，假设你的表叫user_records，CLOB列叫user_json，那语句就是：

SELECT 
  JSON_VALUE(user_json FORMAT JSON, '$.nomeUsuario') AS nome_usuario
FROM 
  user_records;

这样就能精准提取出像"Nilson Roniery da Silva"这样的用户名了。

方法二：老版本Oracle用字符串处理函数 #

如果你的数据库版本低于12c，不支持JSON函数，那就只能用字符串截取的方式来实现。核心思路是找到"nomeUsuario":"的起始位置，再找到它后面第一个双引号的位置，然后截取中间的内容：

SELECT 
  SUBSTR(
    your_clob_column,
    -- 找到目标键值对的起始位置，加上键的长度，跳到值的开头
    INSTR(your_clob_column, '"nomeUsuario":"') + LENGTH('"nomeUsuario":"'),
    -- 计算要截取的长度：从值的开头到下一个双引号的距离
    INSTR(your_clob_column, '"', INSTR(your_clob_column, '"nomeUsuario":"') + LENGTH('"nomeUsuario":"')) - 
    (INSTR(your_clob_column, '"nomeUsuario":"') + LENGTH('"nomeUsuario":"'))
  ) AS nome_usuario
FROM 
  your_table;

⚠️ 注意：这种方法有局限性，如果你的JSON内容里存在转义的双引号（比如"nomeUsuario":"Nilson\"Roniery"），就会提取错误，所以能升级版本用方法一的话尽量用方法一。

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