简介面向Oracle数据库开发与运维人员这份PDF资源聚焦JSON字符串中键值对内容的高效截取围绕自定义函数parsejsonstr的完整实现与调用方式展开讲解能帮助读者快速解决从JSON字段中提取指定键值、确定截取边界等常见问题。资源为单个PDF文件大小仅32KB虽轻量但内容紧凑包含函数源码、参数说明p_jsonstr、startkey、endkey及实际查询示例如SELECT parsejsonstr(INFO,AGE,HEIGHT) FROM TTTT。文中还讨论了endkey为}时的特殊截取逻辑并延伸介绍了Oracle内置的JSON_VALUE、JSON_QUERY等工具便于读者理解不同JSON处理方案的适用场景。目前已有5259人学习下载对于需要在Oracle环境下灵活操作JSON数据的开发者而言是一份简洁实用的参考笔记。1. 当 Oracle 没有 JSON 函数时为什么我还在用 INSTR SUBSTR 截 JSON在 Oracle 11g 或更早的环境里处理 JSON你会发现自己陷入一个尴尬境地没有JSON_VALUE没有JSON_TABLE只有一堆带 JSON 字符串的 VARCHAR2 字段。我在做报表接口对接时经常遇到这种表——整段 JSON 存在一个字段里但甲方只要其中一个 key 的值。这种场景下与其等数据库升级不如写个通用函数一次搞定。PLATFORM.parsejsonstr就是干这个的给定 JSON 字符串、起始 key 和结束 key它用INSTR定位位置、用SUBSTR切出内容纯字符串匹配不依赖任何 Oracle 12c 的 JSON 内置能力。它不像JSON_VALUE那样能解析嵌套结构但对于扁平 JSON 对象这个函数完全够用而且迁移到任何版本的 Oracle 都能跑。本文就把这个函数的实现逻辑拆开讲清楚并给出可直接抄走的建函数脚本、参数对照表和常见踩坑点。2. 拆解 parsejsonstrINSTR 定位 SUBSTR 切片的核心逻辑2.1 函数骨架先从参数设计看这个函数的适用边界先看函数签名CREATE OR REPLACE FUNCTION PLATFORM.parsejsonstr( p_jsonstr VARCHAR2, startkey VARCHAR2, endkey VARCHAR2 ) RETURN VARCHAR2三个参数各司其职。p_jsonstr是整段 JSON 字符串通常直接传表里的 VARCHAR2 字段startkey是要取值那个字段的 key注意是裸字段名不带双引号endkey是目标 key 的下一个 key用来标记截取终点。函数内部其实只用了两次INSTR和一次SUBSTR第一次定位 startkey 出现的位置第二次从 startkey 的位置往后找 endkey两者之间就是目标值所在区间。这个设计有个聪明的点它不数双引号也不管 JSON 里到底有几层括号全靠一个“结束标记”来切边界。代价是 JSON 字段顺序必须固定而且 endkey 必须真实存在于 startkey 之后——不过对于接口对接时甲方给的那种“字段顺序从不变化”的 JSON 报文这反而是最稳定的方案。2.2 两个分支endkey 为}时为什么截取长度要减 2函数里最核心的分支就是IF endkey }两个分支的SUBSTR公式几乎一样唯一差别是截取长度末尾的减法系数不同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;注意一个细节INSTR(p_jsonstr, endkey, INSTR(p_jsonstr, startkey))这里的第三参数指定了搜索起点。也就是说从 startkey 第一次出现的位置开始往后找 endkey避免 endkey 出现在 startkey 之前导致截取区间为负。为什么}分支减 2、其他分支减 4因为函数返回的是带双引号的值。假设 JSON 是{AGE:25,HEIGHT:170}目标 AGE 的值 25 前后各有一个双引号从 AGE 的最后一个字符到值左边界之间还隔着一个冒号和一个双引号所以起始位置要加 2。截取长度方面如果 endkey 是普通 key比如HEIGHT从 AGE 值到 HEIGHT 之间还隔着,和所以减 4如果 endkey 是}中间只隔着所以减 2。这个公式是写死了的也是后续避坑章要说的问题来源。2.3 手工推演一遍一个 JSON 字符串的截取全过程拿原文的例子手工走一遍。假设 TTTT 表的 INFO 字段内容为{AGE:25,HEIGHT:170}调用parsejsonstr(INFO, AGE, HEIGHT)执行过程如下INSTR(p_jsonstr, AGE)返回 AGE 字符串起始位置假设原文从第 3 个字符开始是AGE加上LENGTH(AGE) 2也就是加 5定位到值 25 的左边界即第一个双引号之后INSTR(p_jsonstr, HEIGHT, 起点)从 AGE 位置往后找 HEIGHT找到后其位置减去 AGE 的位置再减去LENGTH(AGE)再减去 4逗号和双引号得到截取长度 2SUBSTR从值左边界开始取 2 个字符结果就是25。如果 endkey 传的是}比如提取最后一个字段步骤一致只是长度系数从 4 变成 2。手工拆完你会发现这个函数本质上是把“两个 key 之间的值”抠出来规则很机械但胜在无依赖、可预估。3. 落地实操建函数、跑查询、参数对照与内置函数选型3.1 建函数脚本直接复制就能用的完整 DDL先把完整建函数脚本放这已按原文修正格式CREATE OR REPLACE FUNCTION PLATFORM.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;几个容易写错的地方提一句FindIdxS和FindIdxE在这个版本里声明了但没实际参与计算不影响执行留着或删掉都行PLATFORM是 schema 名如果当前用户没有这个 schema改成你自己的用户名VARCHAR2(1000)是返回值上限如果 JSON 里的值超过 1000 字符会报ORA-06502需要加长或改成CLOB。3.2 典型调用SELECT 里直接用的三种写法最标准的用法就是原文示例顶多把列名换成实际的SELECT parsejsonstr(INFO, AGE, HEIGHT) AS age_value FROM TTTT;如果 JSON 里只有 AGE 一个键后面紧跟着右花括号endkey 就传}SELECT parsejsonstr(INFO, AGE, }) AS last_value FROM TTTT;如果 JSON 里目标字段位置不确定但下一个 key 是固定的就用固定 endkey。比如接口返回的 JSON 里ADDR后面一定是PHONESELECT parsejsonstr(RESP_JSON, ADDR, PHONE) AS address FROM API_LOG WHERE REQ_ID 20240001;这三种调用的共同前提是目标 key 和 endkey 在 JSON 原文里必须按顺序出现并且 startkey 不能是 endkey 的子串。比如 JSON 里有AGE和PACKAGE两个 key原地用parsejsonstr(INFO, AGE, PACKAGE)会出问题因为INSTR找AGE时可能先命中了PACKAGE里的AGE子串这个坑在第 4 章详述。3.3 参数对照表startkey、endkey 在不同 JSON 结构下的取值JSON 片段startkeyendkey返回结果{AGE:25,HEIGHT:170}AGEHEIGHT25{NAME:张三,AGE:25}NAMEAGE张三{CODE:200,MSG:OK}CODEMSG200{CODE:200,MSG:OK}MSG}OK{A:1,B:2,C:3}BC2{C:3}C}3对照表说明三件事一是返回结果始终带双引号需要干净值时必须再套一层TRIM(BOTH FROM ...)二是取最后一个字段必须传}作为 endkey三是中间字段的 endkey 必须是下一个真实存在的 key不能凭空编。3.4 和内置 JSON_VALUE 的选型对比不是所有场景都该手写函数如果数据库是 Oracle 12c 及以上我一般会优先建议用JSON_VALUE而不是这个自定义函数。原因很直接JSON_VALUE支持路径表达式能穿透嵌套结构而且内部做了 JSON 格式校验字段顺序无关不用指定 endkey。对比表格列一下对比项parsejsonstrJSON_VALUE适用版本所有版本12c字段顺序敏感是否嵌套 JSON不支持支持用路径返回带引号是否容错能力弱靠 INSTR 硬找强路径找不到返回 NULL典型写法parsejsonstr(INFO,AGE,HEIGHT)JSON_VALUE(INFO,$.AGE)什么时候还值得用自定义函数要么是生产库还在 11g要么是那段 JSON 根本不是合法 JSON——比如甲方返回的是{AGE25,HEIGHT170}这种非标格式JSON_VALUE直接报错而parsejsonstr反而能靠字符串定位硬抠出来。我遇到过好几个银行老系统的接口返回这种“伪 JSON”这种时候手写函数是唯一的出路。4. 避坑指南这个函数五个深坑踩过的人都知道4.1 返回值带着双引号你以为拿到的是 25实际是 25现象parsejsonstr(INFO, AGE, HEIGHT)返回的结果是25带引号。如果你拿这个值去跟数字比较、或者插入数字字段直接报类型不匹配或隐式转换异常。原因函数公式里的加 2 和减 2 是为了跳过冒号和双引号但只跳过了值左侧的引号返回区间正好把值右侧的引号也包进来了。这是 substr 截取区间设计的问题不是 bug但非常容易忽略。解决套一层处理或者直接在调用端去掉引号SELECT TRIM(BOTH FROM parsejsonstr(INFO, AGE, HEIGHT)) AS age_clean FROM TTTT;如果是数字再套TO_NUMBER即可。从那以后我每次用这个函数都会先确认返回值的引号问题免得后面报错再回头查。4.2 startkey 命中 key 的子串PACKAGE 里藏着一个 AGE现象JSON 里有AGE和PACKAGE两个字段用parsejsonstr(INFO, AGE, PACKAGE)取 AGE 值结果返回空或取出来一堆乱码。原因INSTR(p_jsonstr, AGE)找的是第一次出现AGE的位置而PACKAGE里包含AGE子串如果 PACKAGE 排在 AGE 前面第一次命中的就是 PACKAGE 里的片段后续所有偏移量全乱。解决把 startkey 传成带引号的完整 key比如AGE这样INSTR找的是AGE不会命中PACKAGE里的子串SELECT parsejsonstr(INFO, AGE, HEIGHT) AS age_value FROM TTTT;注意此时函数里LENGTH(startkey) 2也跟着变了起始偏移会多出 2 个字符结果会多冒出内容。更稳妥的做法是改函数内部逻辑在 startkey 前后拼上双引号再做INSTR但那样会动原有公式建议直接在传参时绕开子串冲突。4.3 字段顺序变了截取结果当场错位现象某天接口方在 JSON 中间插入一个新字段比如原来{AGE:25,HEIGHT:170}变成{AGE:25,WEIGHT:60,HEIGHT:170}用parsejsonstr(INFO,AGE,HEIGHT)取出来的结果是25,WEIGHT:60。原因这个函数完全不理解 JSON 结构它只知道“找两个 key 的文本位置并切中间”中间多出来的任何内容都会被当成值切进去。解决改函数的适用边界——字段顺序稳定的接口才能用它或者改用REGEXP_SUBSTR配合模式匹配比如SELECT REGEXP_SUBSTR(INFO, AGE:([^]*), 1, 1, NULL, 1) AS age_value FROM TTTT;这个方案对字段顺序不敏感只认 AGE 这个 key但前提是 JSON 格式合法且引号转义规范。只能说一句结构会变的 JSON别用定距截取。4.4 endkey 不存在时函数不报错但返回空现象调用parsejsonstr(INFO, AGE, GENDER)JSON 里根本没有 GENDER函数返回 NULL排查半天以为是数据问题实际是 endkey 传错了。原因INSTR(p_jsonstr, GENDER, 起点)找不到时返回 00 参与减法运算后截取长度变成负数SUBSTR的第三个参数为负就会被当成 0 处理返回空字符串。整个过程无异常、无提示很容易被忽略。解决调用前确认 endkey 真实存在于 JSON 里。如果无法确认可以给函数加一层防御比如找不到 endkey 就直接返回 NULLIF INSTR(p_jsonstr, endkey, INSTR(p_jsonstr, startkey)) 0 THEN RETURN NULL; END IF;在生产环境我会先对样本数据执行一次INSTR检查再跑批量查询避免几百条数据全是空结果。4.5 json 字符串里有空格或换行就翻车现象JSON 是格式化过的比如字段之间带换行、冒号后面带空格parsejsonstr截取结果里夹带着\n和空格甚至截取长度直接算错。原因公式里的“加 2”“减 4”都是按紧凑 JSON 的字符数设计的多了空格或换行会让冒号和引号的实际位置偏移截取区间就错位了。解决传参前先压缩字符串去掉空白字符再截取SELECT parsejsonstr( REGEXP_REPLACE(INFO, [[:space:]], ), AGE, HEIGHT ) AS age_clean FROM TTTT;如果 JSON 字符串中包含正常的空格比如值的中间有空格这个方案会误删内容建议只在确认值是纯数字或纯字母时使用。更多时候遇到格式化 JSON 我直接改用JSON_VALUE比在这硬调偏移量省心得多。5. 进阶技巧返回纯值版封装与多层 JSON 的降级方案如果确实要长期依赖这个函数建议在原函数基础上做一个封装版本让它能处理带引号的 key 和转义双引号输出干净的纯值CREATE OR REPLACE FUNCTION PLATFORM.parsejsonstr_clean( p_jsonstr VARCHAR2, startkey VARCHAR2, endkey VARCHAR2 ) RETURN VARCHAR2 IS v_json VARCHAR2(4000); v_key VARCHAR2(100); v_start NUMBER; v_end NUMBER; v_val VARCHAR2(1000); BEGIN v_json : REGEXP_REPLACE(p_jsonstr, [[:space:]], ); v_key : || REPLACE(startkey, , ) || ; IF endkey } THEN v_start : INSTR(v_json, v_key) LENGTH(v_key) 1; v_end : INSTR(v_json, }, v_start); ELSE v_start : INSTR(v_json, v_key) LENGTH(v_key) 1; v_end : INSTR(v_json, || REPLACE(endkey, , ) || , v_start) - 2; END IF; IF v_start 0 OR v_end 0 THEN RETURN NULL; END IF; v_val : SUBSTR(v_json, v_start, v_end - v_start); RETURN TRIM(BOTH FROM v_val); END parsejsonstr_clean;这个封装版主要改了三处startkey 自动拼双引号避免子串误命中用右花括号或 endkey 前两位做终点省掉手工算偏移最后统一去掉首尾引号。用起来干净得多SELECT parsejsonstr_clean(INFO, AGE, HEIGHT) AS age_value FROM TTTT;如果 JSON 是嵌套结构比如{USER:{AGE:25,HEIGHT:170}}这个系列函数都扛不住。建议降级用JSON_VALUESELECT JSON_VALUE(INFO, $.USER.AGE) AS age_value FROM TTTT;如果库版本不支持 JSON_VALUE就只能先按USER:{作为 startkey 把子对象粗切出来再递归调一次parsejsonstr_clean取里层的 AGE。这个方案很绕但只要字符串结构稳定也算能跑通。最后分享一个自己的习惯用这个函数之前我一定先看一眼真实数据的 JSON 片段确认字段顺序和紧凑格式再决定要不要加REGEXP_REPLACE做清洗。我吃过太多次“脚本本地跑得好好的一上生产就错位”的亏——基本都是忽略了数据里有不可见字符。希望你这次部署时能一次跑通。希望这篇拆解帮到你。本文还有配套的精品资源点击获取