简介：面向Oracle数据库开发与运维人员，这份PDF资料聚焦在PL/SQL中提取JSON字符串指定键值这一常见需求，以自编函数parsejsonstr为例，讲解如何通过起始键与结束键精确截取目标内容，适用于数据清洗、接口联调、应用集成等场景。
压缩包仅含1个PDF文件，整体大小32KB，内容精炼便于本地速查。文档完整给出函数创建语句、三个入参（目标JSON串、起始键、结束键）的语义说明，以及select parsejsonstr(INFO,'AGE','HEIGHT') from TTTT这类落地示例，并对endkey为右花括号或下一键两种情形分别演示截取边界的实现逻辑；同时可结合Oracle内置JSON_VALUE、JSON_QUERY对比学习，帮助理解自定义解析与标准函数各自适用边界。
资料目前已有5259人学习，适合需要快速掌握Oracle自定义JSON解析方法、并希望了解截取边界细节的中高级开发者。
1. Oracle截取JSON字符串内容：先分清“截”和“解”，避免在SQL里手撕大括号
Oracle截取JSON字符串内容这件事，难的不是不会写SQL，而是先想清楚要“截”还是要“解”。我见过不少报表组同事拿到一列JSON，第一反应就是INSTR加SUBSTR，再拼一个TO_NUMBER，三个函数把字段硬拉出来。数据量小、格式固定的情况下确实快；但一旦JSON里出现嵌套对象、同名键、转义字符，这套写法就变成定时炸弹。这篇文章把字符串截取、内置JSON函数、JSON_TABLE展开三条路径的适用边界、可复现命令和高频坑位一次讲清，适合需要从接口日志、订单明细或配置表中把JSON字段加工成业务字段的开发与DBA。
2. 用INSTR+SUBSTR截取JSON字段值：最快，但只适合结构固定的数据
2.1 定位键名再截值：一段能跑的SUBSTR样例
字符串截取JSON的原理很简单：找键名，取键名后面那一段，再按引号或逗号把值切出来。看一段最基础的写法：
-- 从 payload 字段里取 "name" 键对应的值 -- 假设存储的是紧凑 JSON，形如 {"name":"alice","age":30} SELECT SUBSTR( payload, INSTR(payload, '"name"') + 7, INSTR(SUBSTR(payload, INSTR(payload, '"name"') + 7), '"') - 1 ) AS name_val FROM t_api_log;这段SQL的执行逻辑是：INSTR先找到键名"name"第一次出现的字符位置，加7是因为"name"本身占6个字符，后面还跟着一个冒号，所以加7后正好落在值区域的起始位置。外层SUBSTR从这个位置开始截，截多长由内层INSTR决定——内层INSTR在剩下的字符串里找第一个双引号，它的位置减1就是值的长度。
这个写法有个硬前提：JSON必须是紧凑格式，键和值之间没有多余空格。如果接口方在冒号后补了一个空格，加7后指向的就是空格而不是值，截出来的结果就是空字段或半个字段。所以在真实表上跑之前，先看一眼样本数据有没有空格，再用LENGTH()验证一下结果。
2.2 用REGEXP_SUBSTR收编不规整数据
冒号后带空格、值里带特殊字符的情况，INSTR容易翻车，常见做法是换成正则。REGEXP_SUBSTR是Oracle正则家族里最适合干这个活的函数：
-- 用正则处理冒号后带空格的情况 -- 第6个参数 1 表示返回第一个捕获组，也就是去掉外层引号后的值 SELECT REGEXP_SUBSTR( payload, '"name"[[:space:]]*:[[:space:]]*"([^"]*)"', 1, 1, NULL, 1 ) AS name_val FROM t_api_log;这段模式里[[:space:]]*匹配冒号前后的任意空格，[^"]*从值开头一直吞到下一个双引号前，括号把真正要的值包成捕获组。需要注意REGEXP_SUBSTR的第六个参数是返回第几个捕获组：不写它返回整段匹配结果，写了1就只返回括号里的内容。
取数字字段时模式略有不同：
-- 取 "age" 的数字值 -- 匹配整数，如果出现负数或小数，把 [0-9]+ 换成 -?[0-9]+(\.[0-9]+)? SELECT REGEXP_SUBSTR( payload, '"age"[[:space:]]*:[[:space:]]*([0-9]+)', 1, 1, NULL, 1 ) AS age_val FROM t_api_log;正则方案弥补了INSTR对空格的敏感，但代价是匹配过程更耗CPU。百万行级别的表上做全表扫描，正则的耗时明显比INSTR方案高，只适合临时排查或数据量可控的加工任务。如果是在存储过程里反复调用，别这么做——后面会讲到JSON_OBJECT_T的PL/SQL原生解析方式。
2.3 字符串方案的边界：嵌套、重复键、转义三条红线
讲完可复现的写法，必须把边界说透。字符串截取JSON有三条红线：
第一，嵌套对象。取address.city时，SUBSTR按引号和逗号做定位，但内层对象自己也有引号和逗号，切到一半就断了。第二，同名键。一个JSON里既有customer.name又有items[0].name，INSTR只认字符串出现次序，不认识层级，永远返回第一个。第三，转义字符。值里出现\"或\uXXXX时，SUBSTR会在转义引号处把值切断，正则虽然能靠模式扛一部分，但写起来非常痛苦。
另外注意，“字符串截取前两位”这种惯用法在JSON里不成立——SUBSTR直接取前两位只会把键名和冒号一起截出来，没有任何解析价值。我的判断标准是：结构固定、键名唯一、无嵌套、值域简单，这四个条件同时满足才用字符串函数；只要有一个不满足，就进入下一章的JSON内置函数。
3. 用JSON_VALUE/JSON_QUERY按路径取数：性能和准确性都更稳
3.1 JSON_VALUE取标量值并处理缺失键
Oracle从12c开始提供原生JSON函数，JSON_VALUE取标量值是日常最顺手的工具。它的核心是路径表达式：$代表整段JSON，点号下钻一层，方括号是数组下标。
-- 取顶层字段、嵌套字段、数组元素三个典型路径 SELECT JSON_VALUE(payload, '$.name') AS name_val, JSON_VALUE(payload, '$.address.city') AS city_val, JSON_VALUE(payload, '$.items[0].price') AS first_item_price FROM t_api_log;这三行对应的JSON结构分别是{"name":"alice"}、{"address":{"city":"杭州"}}和{"items":[{"price":12.5}]}。键不存在时JSON_VALUE返回NULL，不会报错，这一点比字符串截取更安全。
实际业务里更常遇到“键不存在时给默认值”的需求。12.2以后可以这样写：
-- 键缺失时返回 'unknown' -- 类型不匹配或解析失败时返回 'bad_json' SELECT JSON_VALUE( payload, '$.name' DEFAULT 'unknown' ON EMPTY ) AS name_val, JSON_VALUE( payload, '$.age' RETURNING NUMBER DEFAULT 0 ON ERROR ) AS age_num FROM t_api_log;ON EMPTY处理键不存在或数组越界，ON ERROR处理类型转换失败、JSON格式非法等情况。RETURNING NUMBER指定返回类型，配合DEFAULT 0 ON ERROR，相当于Oracle里“过滤不可转为数字的字符串”在JSON场景的答案——转换不了就给默认值，而不是让整个查询中断。
3.2 JSON_QUERY取对象和数组：为什么结果带着引号
JSON_VALUE只适合取标量值，要取数组或对象片段时得换JSON_QUERY，两者分工不同：
-- 对比两个函数在同一字段上的返回结果 SELECT JSON_VALUE(payload, '$.name') AS name_val, JSON_QUERY(payload, '$.tags') AS tags_json FROM t_api_log;如果JSON是{"name":"alice","tags":["a","b"]}，name_val返回alice，tags_json返回["a","b"]，两层结果完全不一样。这里有个高频坑：有人拿JSON_QUERY取$.name，发现返回的不是alice而是"alice"，两侧带着双引号。原因是JSON_QUERY的语义是返回JSON文档片段，字符串在JSON里本身就带引号，它不是出错，而是设计如此。所以要记住一条分工：标量用JSON_VALUE，数组和对象用JSON_QUERY。
3.3 用RETURNING控制返回类型并建索引
JSON_VALUE默认返回VARCHAR2，很多人在拿到值之后又做一次TO_NUMBER或TO_DATE，其实RETURNING子句可以直接完成转换：
-- 直接返回 NUMBER 类型，省掉一层 TO_NUMBER SELECT JSON_VALUE( payload, '$.age' RETURNING NUMBER ) AS age_num FROM t_api_log;这里的RETURNING支持VARCHAR2、NUMBER、DATE等常见类型。日期字段建议先用VARCHAR2过渡再TO_DATE，因为JSON里的日期字符串格式五花八门，让JSON_VALUE直接转DATE容易撞上格式不匹配的ON ERROR分支。如果这个JSON字段会被高频等值查询，可以在JSON_VALUE上建函数索引：
-- 用虚拟列固化 JSON_VALUE 的结果，再建索引 ALTER TABLE t_api_log ADD name_vc AS (JSON_VALUE(payload, '$.name' RETURNING VARCHAR2(100))); CREATE INDEX idx_t_api_log_name ON t_api_log(name_vc);加了虚拟列之后，每次DML写入都会多一次JSON解析开销，所以只适合读多写少的表。这个代价换来的是等值查询不再全表扫描，对接口日志、订单快照这类数据很划算。
4. 用JSON_TABLE把JSON数组展开成行：报表和数据搬运的标配
4.1 基础展开：从订单表拆出明细行
字符串截取和JSON_VALUE解决的都是一行JSON取一个值。真要面对“一行订单里有items数组，要拆成多行明细”这种需求，就得用JSON_TABLE。常见场景是订单表的payload里存了商品明细，SQL里要把数组每个元素展开成一行：
SELECT o.order_id, d.item_id, d.item_name, d.price FROM t_orders o, JSON_TABLE( o.payload, '$.items[*]' COLUMNS( item_id VARCHAR2(32) PATH '$.id', item_name VARCHAR2(200) PATH '$.name', price NUMBER PATH '$.price' ) ) d WHERE o.order_id = 1001;JSON_TABLE后面那串路径$.items[*]意思是把items数组的所有元素都展开，[*]是通配下标；COLUMNS里每列通过PATH指定往下一层的哪个字段。这段SQL用的是逗号连接，等效于INNER JOIN，实际含义是只有能被JSON_TABLE解析出行的订单才会出现在结果里。
这里容易踩一个逻辑陷阱：如果订单的items是空数组，整张订单会因为内连接被过滤掉。要保留空数组订单，用LEFT OUTER JOIN写法：
-- 保留 items 为空的订单，明细字段为 NULL SELECT o.order_id, d.item_id FROM t_orders o LEFT OUTER JOIN JSON_TABLE( o.payload, '$.items[*]' COLUMNS(item_id VARCHAR2(32) PATH '$.id') ) d ON 1 = 1 WHERE o.order_id = 1001;ON 1 = 1是JSON_TABLE做外连接时的常用手法，含义是无条件保留左表所有行。做订单报表时这个写法几乎是标配，否则会莫名其妙的少订单。
4.2 NESTED PATH展开嵌套数组：一对多对多
如果items里还有一层skus数组，要一次性展开成“订单 → 商品 → SKU”的三级明细，就得用NESTED PATH：
-- 同时展开 items 和 items.skus 两层数组 SELECT o.order_id, d.item_id, d.sku_id, d.price FROM t_orders o, JSON_TABLE( o.payload, '$.items[*]' COLUMNS( item_id VARCHAR2(32) PATH '$.id', NESTED PATH '$.skus[*]' COLUMNS ( sku_id VARCHAR2(32) PATH '$.skuId', price NUMBER PATH '$.price' ) ) ) d;NESTED PATH的作用是在外层数组元素的基础上继续展开内层数组，展开后的行数等于外层元素数乘以内层元素数，正好对应“一对多对多”的关联关系。路径层级加深之后，能明显感觉到JSON_TABLE比字符串函数好用的地方：路径写清楚，层级不会乱，也不会出现SUBSTR截到半截花括号的问题。
4.3 什么时候该换JSON_TABLE而不是字符串截取
几个典型场景做个对比，基本能覆盖日常判断：
| 需求类型 | 推荐做法 | 原因 |
|---|---|---|
| 临时看一眼某个键的值 | JSON_VALUE或字符串函数 | 写起来短，排查够用 |
| 一行JSON要取5个以上标量字段 | JSON_TABLE | 一张虚拟表一次拿完，代码清晰 |
| 数组元素要展开成明细行 | JSON_TABLE | 字符串截取根本办不到 |
| 按数组里的元素做过滤和聚合 | JSON_TABLE | 展开后可以正常WHERE和GROUP BY |
| PL/SQL存储过程里解析 | JSON_OBJECT_T/JSON_ARRAY_T | 原生类型操作比SQL函数拼接更稳 |
从性能角度说，同一个JSON如果在一个查询里被5个JSON_VALUE各解析一遍，不如用JSON_TABLE一次展开再投影，路径越多差距越明显。这个习惯在数据仓库加工任务里尤其值钱。
5. Oracle截取JSON字符串常见问题排查：5个真实踩坑记录
5.1 JSON_QUERY取标量带回引号
现象：SELECT JSON_QUERY(payload, '$.name')返回"alice"，两侧带着双引号，拼接字符串时变成name="alice"，下游程序把引号当成了数据。
原因：JSON_QUERY的语义是返回JSON文档片段，字符串在JSON规范里必须带引号。这不是函数出错，是很多人把它和JSON_VALUE搞混了。
解决：取标量值一律用JSON_VALUE；JSON_QUERY只用来取数组或对象。如果历史SQL已经大量使用JSON_QUERY取标量且不方便改，可以用TRIM(BOTH '"' FROM ...)临时处理，但要注意值里如果真的包含转义引号，TRIM会留下病态数据。
5.2 字符串截取嵌套字段截出半截JSON
现象：用SUBSTR取address.city，结果不是城市名，而是{"city":"杭州"这种残缺片段，或者只取到address对象里第一个字段的值。
原因：SUBSTR按引号和逗号定位，不理解JSON的层级结构。遇到内层对象时，它先碰到了内层对象的引号或逗号，自然提前终止。
解决：嵌套路径直接用JSON_VALUE(payload, '$.address.city')。如果在存储过程里遇到同样需求，用JSON_OBJECT_T.parse(payload).get_String('address')再往下一层取，比在SQL里拼字符串可靠得多。
5.3 TO_NUMBER(SUBSTR())导致长数字精度丢失
现象：取出来的ID字段12345678901234567890，经过TO_NUMBER之后变成12345678901234567000，末尾几位丢了。
原因：SUBSTR截出来的是字符串，如果为了排序或比较又套了一层TO_NUMBER，Oracle的NUMBER在超过精度上限时会做近似舍入，问题出在TO_NUMBER，不在截取。
解决：ID、订单号这类标识符全程保持VARCHAR2，不要转数字。需要数值语义时用JSON_VALUE(payload, '$.id' RETURNING NUMBER)，由JSON解析器完成转换，同时配上DEFAULT 0 ON ERROR避免脏数据中断查询。
5.4 同名键多次出现：INSTR永远取到第一次
现象：payload里既有customer.name，又有items[0].name，SUBSTR结果永远是customer.name的值，怎么调都没法取到后一个。
原因：INSTR的匹配单位是“字节串第几次出现”，不是“JSON路径第几个节点”。JSON的层级信息在字符串层面是不存在的，同名键越多越分不清。
解决：出现同名键或数组结构就放弃字符串函数，改用JSON_VALUE(payload, '$.items[0].name')这种路径写法。数组元素的层级通过方括号下标表达，和INSTR的occurrence参数完全不是一回事。同级多个元素需要逐个取时，优先考虑JSON_TABLE展开。
5.5 JSON函数报格式错误：脏数据与解析器边界
现象：payload字段前面带了INFO:日志前缀，或者整段是散文本加JSON的混合物，JSON_VALUE直接抛ORA-404xx系列错误；反倒是字符串截取能抠出片段。
原因：JSON_VALUE、JSON_TABLE要求整段输入是合法JSON，日志前缀、换行符、非JSON文本都会让解析器中断。这是解析器的严格边界，不是函数写得不对。
解决：在查询里先用REGEXP_SUBSTR(payload, '\{.*\}')把JSON体从脏文本里抓出来，但这种做法只适合临时救火；真正可靠的是入库阶段就加约束清洗，下一章就给方案。
6. 从截取到加工：数据清洗、落宽表与性能习惯
前面几种截取和解析方式讲完，最后补一个稳定性的收尾。先做一次数据健康检查，很多“解析报错”其实不用到查询阶段才暴露：
-- 检查整表 JSON 合法率和脏数据占比 SELECT COUNT(*) AS total_cnt, SUM(CASE WHEN payload IS JSON THEN 1 ELSE 0 END) AS json_cnt, SUM(CASE WHEN payload LIKE 'INFO:%' THEN 1 ELSE 0 END) AS dirty_cnt FROM t_api_log;payload IS JSON是Oracle内置的JSON格式校验表达式，可以在WHERE和CHECK约束里直接用。检查完如果脏数据比例高，加一条约束从源头堵住：
-- 从入库阶段保证 payload 必须是合法 JSON ALTER TABLE t_api_log ADD CONSTRAINT t_api_log_json_ck CHECK (payload IS JSON);这条约束加上去，非法JSON根本进不了表，后面所有JSON_VALUE、JSON_TABLE查询都不需要再担心格式问题。
加工成宽表的场景，我习惯先把解析结果落成一张扁平表，避免业务查询反复解析同一段JSON：
-- 把 JSON 字段解析成扁平列，一次解析多次使用 INSERT INTO t_api_log_flat(order_id, name, age, first_item_price) SELECT order_id, JSON_VALUE(payload, '$.name'), JSON_VALUE(payload, '$.age' RETURNING NUMBER DEFAULT 0 ON ERROR), JSON_VALUE(payload, '$.items[0].price' RETURNING NUMBER) FROM t_api_log WHERE payload IS JSON;落表之前先跑COUNT比对源表和目标表的行数，再抽查几个关键字段的NULL率，确认无误再放开给下游。这个习惯帮我挡掉过好几次生产事故。自己的教训是：分割字符串做临时取数没问题，但凡是要落库、要出报表的JSON加工，统一走JSON_VALUE/JSON_TABLE，稳比快重要，希望帮到你。
本文还有配套的精品资源，点击获取
