简介这份PDF文档聚焦Oracle数据库中截取JSON字符串内容的实用方法面向需要处理JSON数据的数据库开发人员与运维工程师。内容围绕自定义函数parsejsonstr展开详细讲解如何通过p_jsonstr、startkey、endkey三个参数从JSON字符串中提取指定键值对并针对endkey是否为右花括号分别说明截取逻辑配合完整SQL示例演示从INFO字段中提取AGE信息的过程。资源包共1个PDF文件大小约32KB篇幅精炼便于快速查阅与复用。目前已有5259人学习下载说明该方案在实际开发中具有较高的参考价值。读者可从中掌握自定义JSON截取函数的编写思路与调用方式理解Oracle内置JSON_VALUE、JSON_QUERY等函数与自定义方案的适用边界为处理复杂JSON结构提供可落地的技术参考。1. 为什么老系统里还在用 parsejsonstr 截 JSON很多 Oracle 老库10g、11g 那批根本没有JSON_VALUE、JSON_QUERY这些原生函数可业务表里偏偏塞了一列 CLOB 或 VARCHAR2里面躺着一段 JSON。你要从里面抠出AGE、HEIGHT这种字段又不想动应用层最省事的办法就是写一个 PL/SQL 函数用INSTR定位、用SUBSTR截取。parsejsonstr就是干这个的传进去整段 JSON、起始 key、结束 key它把中间那段值还给你。它适合谁适合维护 Oracle EBS、老 ERP、接口日志表的一线人适合临时取数、做数据核对、写报表 SQL 的场景。它不优雅但能跑而且不用改表结构、不用升级数据库版本。下面我把它拆开讲清楚包括参数怎么设、边界在哪、什么时候会翻车。2. parsejsonstr 函数拆解INSTR 定位与 SUBSTR 截取的参数逻辑2.1 函数签名与三个入参到底怎么传先把原始代码摆出来这是整篇的核心后面所有讨论都围绕它。CREATE OR REPLACE FUNCTION PLATFROM.parsejsonstr( p_jsonstr varchar2, startkey varchar2, endkey varchar2 ) RETURN VARCHAR2 IS rtnVal VARCHAR2(1000); FindIdxS NUMBER(2); FindIdxE NUMBER(2); BEGIN if endkey } then rtnVal : substr( p_jsonstr, (instr(p_jsonstr, startkey) length(startkey) 2), (instr(p_jsonstr, endkey, instr(p_jsonstr, startkey)) - instr(p_jsonstr, startkey) - length(startkey) - 2) ); else rtnVal : substr( p_jsonstr, (instr(p_jsonstr, startkey) length(startkey) 2), (instr(p_jsonstr, endkey, instr(p_jsonstr, startkey)) - instr(p_jsonstr, startkey) - length(startkey) - 4) ); end if; RETURN rtnVal; END parsejsonstr; /三个参数的含义必须掰开说。p_jsonstr是目标 JSON 字符串注意它可以是列、可以是变量但类型得能隐式转成 VARCHAR2CLOB 直接传会报错得先TO_CHAR或DBMS_LOB.SUBSTR转一道。startkey是你要截取的那个 key 名比如AGE注意这里传的是不带引号的裸 key函数内部靠INSTR找它在字符串里的位置。endkey是目标 key 的下一个 key用来确定截到哪停比如HEIGHT。关键在2和-4这两个魔数。2是因为 JSON 里 key 后面跟着:冒号加引号正好两个字符所以起始位置要跳过key本身长度再加 2。-4出现在 else 分支是因为截取长度算到endkey起始位置后还要减掉,这四个字符逗号、引号、冒号、引号才能把值干净地切出来。当endkey是}时说明目标 key 是对象里最后一个字段后面没有逗号只有}所以只减 2。2.2 两个分支的差异endkey 是}还是普通 key这个 if/else 是整个函数最容易看漏的地方。很多人复制完代码直接跑发现最后一个字段截出来多一个引号或者少一个字符就是没理解分支条件。当endkey }意味着你要截的 key 是 JSON 对象里最后一个成员它后面直接跟}没有逗号。此时截取长度公式是instr(p_jsonstr, }) - instr(p_jsonstr, startkey) - length(startkey) - 2减 2 是去掉:两个字符。而当endkey是普通 key比如HEIGHT目标值后面跟着,四个字符所以减 4。我一般会先确认目标 key 在 JSON 里的位置如果它后面还有别的 key就传那个 key 当endkey如果它是最后一个就传}。这一步判断错了截出来的值要么带尾巴要么被砍掉一位。2.3 一个可复现的调用例子假设TTTT表里INFO列存了这样一段{NAME:张三,AGE:28,HEIGHT:175,CITY:杭州}要取AGE因为AGE后面还有HEIGHT所以endkey传HEIGHTSELECT parsejsonstr(INFO, AGE, HEIGHT) AS AGE_VAL FROM TTTT;返回结果是28。如果要取CITY它是最后一个字段endkey传}SELECT parsejsonstr(INFO, CITY, }) AS CITY_VAL FROM TTTT;返回杭州。注意这里假设值本身不带引号嵌套如果值是28这种带引号的字符串截出来会连引号一起带出来需要自己再REPLACE或TRIM处理。这是这个函数的一个天然边界后面避坑章节会细说。2.4 为什么不用 Oracle 原生 JSON 函数有人会问现在 Oracle 12c 以上都有JSON_VALUE了为什么还用这个土办法。原因很现实一是老库版本不够12c 之前的库根本没有 JSON 函数二是有些列存的是「伪 JSON」格式不规范原生函数直接报 ORA-40441 之类的错反而这种字符串截取能硬扛过去三是临时取数场景写个SELECT parsejsonstr(...)比构造JSON_TABLE快得多。但边界也清楚它只适合结构简单、key 不重复、值里不含嵌套对象的 JSON。复杂结构还是得上原生函数或应用层解析。3. 把函数用进 SQL 与批量取数从单条到整表的落地写法3.1 建函数与权限别在错误的 schema 下创建原始代码里函数名带了PLATFROM.前缀说明它建在PLATFROM这个 schema 下。如果你直接在自己的 schema 里执行要么去掉前缀要么先确认有CREATE PROCEDURE权限。常见做法是-- 切换到目标 schema 或加上 schema 前缀 CREATE OR REPLACE FUNCTION PLATFROM.parsejsonstr( p_jsonstr varchar2, startkey varchar2, endkey varchar2 ) RETURN VARCHAR2 IS rtnVal VARCHAR2(1000); BEGIN -- 函数体同上此处省略 RETURN rtnVal; END; /建完之后要授权否则别的用户查不了GRANT EXECUTE ON PLATFROM.parsejsonstr TO YOUR_USER;这里有个坑rtnVal声明的是VARCHAR2(1000)如果截出来的值超过 1000 字符会直接报 ORA-06502 值错误。JSON 里如果有个长文本字段这个函数就废了。我一般会把它改成VARCHAR2(4000)或者干脆返回 CLOB但改返回类型会影响调用方得评估。3.2 在 SELECT 里批量提取字段单条取数只是验证真正干活是整表批量提。假设TTTT表有ID和INFO两列要一次性把AGE和HEIGHT都拉出来SELECT ID, parsejsonstr(INFO, AGE, HEIGHT) AS AGE_VAL, parsejsonstr(INFO, HEIGHT, CITY) AS HEIGHT_VAL, parsejsonstr(INFO, CITY, }) AS CITY_VAL FROM TTTT WHERE INFO IS NOT NULL;这里每个字段都要单独调一次函数意味着同一段 JSON 被INSTR扫了多遍。数据量小无所谓几十万行以上就会明显变慢。优化思路是先用一次SUBSTR把目标对象整段切出来存到临时表或 WITH 子句再在子集上反复截取。但多数取数场景数据量不大直接这么写够用。3.3 处理 key 重复与大小写问题INSTR是大小写敏感的JSON 里写的是age你传AGE永远找不到返回 NULL。这是最常见的「函数没报错但取不到值」的原因。解决办法有两个一是传参时严格匹配 JSON 里的 key 大小写二是改函数内部把INSTR换成INSTR(UPPER(p_jsonstr), UPPER(startkey))但这样位置会偏移因为UPPER不改变长度位置还对得上可以这么改。另一个问题是 key 重复。如果 JSON 里有两个AGEINSTR只找第一个截出来的是第一段。这种脏数据在老系统里不少见函数本身没法区分只能靠数据清洗或换用能解析完整 JSON 的方案。3.4 用 WITH 子句做中间结果减少重复扫描如果一条 SQL 里要取五六个字段可以把 JSON 先物化一次WITH src AS ( SELECT ID, INFO FROM TTTT WHERE INFO IS NOT NULL ) SELECT ID, parsejsonstr(INFO, NAME, AGE) AS NAME_VAL, parsejsonstr(INFO, AGE, HEIGHT) AS AGE_VAL, parsejsonstr(INFO, HEIGHT, CITY) AS HEIGHT_VAL FROM src;WITH子句在 Oracle 里不一定物化但至少让 SQL 结构清晰方便后续加过滤条件。真正要提速还是得把 JSON 拆成关系表存起来那是另一个话题了。4. 避坑与排查parsejsonstr 最容易翻车的五个场景4.1 取不到值返回 NULL现象SQL 不报错但结果列全是空。原因startkey大小写和 JSON 里不一致或者 JSON 里 key 带了空格、换行。解决先用SELECT INFO FROM TTTT WHERE ID xxx把原始 JSON 打出来肉眼确认 key 的准确拼写包括引号位置。如果 JSON 是格式化过的带换行缩进INSTR找 key 没问题但2的偏移会因为换行符而错位截出来带一堆空白。这种情况建议先REPLACE(INFO, CHR(10), )去掉换行再传。4.2 截出来的值多一个引号或逗号现象结果是28而不是28或者28,带个逗号。原因endkey传错或者值本身在 JSON 里就是带引号的字符串。解决确认endkey是目标 key 的下一个 key不是目标 key 自己。如果值本身带引号外面套一层REPLACE(parsejsonstr(...), , )去掉。但要注意如果值内部本来就有引号比如文本里含REPLACE会误伤得用TRIM(BOTH FROM ...)只去首尾。4.3 ORA-06502 值错误现象函数执行报 ORA-06502: PL/SQL: numeric or value error。原因截出来的值超过VARCHAR2(1000)上限。解决把rtnVal改成VARCHAR2(4000)或者返回 CLOB。改完记得重新编译函数并检查调用方有没有对返回值长度做假设。4.4 JSON 里有嵌套对象截取结果错乱现象目标 key 的值本身是个{...}对象截出来只有一半。原因INSTR找endkey时如果嵌套对象里也有同名的 key会定位到内层去。解决这种结构parsejsonstr处理不了别硬扛。要么用 Oracle 12c 以上的JSON_QUERY要么在应用层用 Python、Java 解析。我一般遇到嵌套超过一层的 JSON直接放弃 SQL 截取走 ETL 抽到中间表再处理。4.5 性能问题全表扫描加多次 INSTR现象几十万行的表取三个字段跑了十几分钟。原因每行每字段都做多次INSTR且INFO列如果是 CLOBINSTR在 LOB 上效率更低。解决先加WHERE条件缩小范围比如按日期分区过滤或者把 JSON 解析结果落到临时表用INSERT INTO ... SELECT一次性算完后续查询走临时表。如果INFO是 CLOB考虑加函数索引或全文索引但函数索引对parsejsonstr这种自定义函数支持有限得实测。5. 进阶把 parsejsonstr 改造成更稳的版本原始函数能跑但边界太脆。我在生产里一般会做三处加固这里把改法写出来你可以直接抄。第一处处理大小写和换行CREATE OR REPLACE FUNCTION PLATFROM.parsejsonstr_v2( p_jsonstr varchar2, startkey varchar2, endkey varchar2 ) RETURN VARCHAR2 IS v_json VARCHAR2(32767); v_start NUMBER; v_end NUMBER; rtnVal VARCHAR2(4000); BEGIN -- 去掉换行和回车统一大写便于定位 v_json : UPPER(REPLACE(REPLACE(p_jsonstr, CHR(10), ), CHR(13), )); v_start : INSTR(v_json, UPPER(startkey)); IF v_start 0 THEN RETURN NULL; -- key 不存在直接返回空不报错 END IF; v_end : INSTR(v_json, UPPER(endkey), v_start); IF v_end 0 THEN RETURN NULL; END IF; -- 统一按带引号值的格式截取再去掉首尾引号 rtnVal : SUBSTR(v_json, v_start LENGTH(startkey) 2, v_end - v_start - LENGTH(startkey) - 4); RETURN TRIM(BOTH FROM rtnVal); END; /改动点说明UPPER统一大小写REPLACE去换行v_start 0和v_end 0做空值保护避免SUBSTR负数长度报错。TRIM(BOTH FROM ...)去掉值首尾的引号比REPLACE安全。返回类型放宽到 4000。第二处如果值可能是数字或布尔截出来是字符串需要转换时在调用层做SELECT ID, TO_NUMBER(parsejsonstr_v2(INFO, AGE, HEIGHT)) AS AGE_NUM FROM TTTT WHERE REGEXP_LIKE(parsejsonstr_v2(INFO, AGE, HEIGHT), ^[0-9]$);加REGEXP_LIKE是为了防止空值或非数字导致TO_NUMBER报错这是血泪经验直接转经常翻车。第三处验证方法。改完函数别急着上生产先造几条边界数据测测试场景JSON 样例预期结果普通字段{AGE:28,HEIGHT:175}28最后字段{AGE:28}28key 不存在{NAME:张三}NULL带换行{AGE:\n28}28值带引号{AGE:\28\}28跑完这五条基本能确认函数在你的数据上稳不稳。从那以后我每次改这类字符串截取函数都强制走一遍这个边界表不再靠「看着差不多」上线。希望帮到你。本文还有配套的精品资源点击获取
