胖头鱼的技术专栏-441 26ai新特性实战：SQL/JSON路径表达式支持DECODE与CASE（20260708）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database
PostgreSQL ACE
10年+数据库行业经验
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP，ITPUB认证专家
圈内拥有“总监”称号，非著 名社恐（社交恐怖分子）
全网同名：胖头鱼的鱼缸
ITPUB：yhw1809
除授权转载并标明出处外，均为“非法”抄袭

特性介绍

Oracle AI Database 23.26.2在SQL/JSON路径表达式中新增了对DECODE和CASE函数的支持。这两个函数分别类似于SQL中的DECODE函数和CASE表达式，但它们在JSON路径表达式内部工作，主要用于JSON_TRANSFORM的右侧（RHS）路径表达式中，实现对JSON数据的条件逻辑处理。

函数介绍

decode()函数

语法：decode(expr, match1, result1, match2, result2, …, [default])
行为：将expr依次与match值比较，返回第一个匹配的result无匹配时返回default（可选）；无default时返回null
特点：基于离散值的精确匹配，适合枚举类型映射

case()函数

语法：case(cond1, result1, cond2, result2, …, [default])
行为：按顺序评估布尔条件，返回第一个为真的条件对应的result无条件为真时返回default（可选）；无default时返回 null
特点：短路求值，支持范围比较和复杂条件

使用场景

• JSON文档增强：根据JSON字段值自动添加分类标签字段例如根据salary字段添加tier（low/medium/high）
• 数据脱敏与转换：在JSON_TRANSFORM中对敏感数据做条件处理例如根据部门代码映射为可读的部门名称
• JSON数据迁移：将旧格式的枚举值批量转换为新格式
• 业务规则嵌入：将条件逻辑直接嵌入路径表达式，避免额外SQL层处理
• REST API后端：在UPDATE操作中根据JSON内容动态设置字段值

实战演示

同之前一样，使用用户NFTEST，已授权DB_DEVELOPER_ROLE角色，测试操作均在NFTEST用户下执行。

创建测试表并添加测试数据

CREATE TABLE json_path_test (
  id   NUMBER PRIMARY KEY,
  data JSON);
INSERT INTO json_path_test VALUES (1, '{"name":"Alice","age":30,"salary":50000,"dept":"Sales"}');
INSERT INTO json_path_test VALUES (2, '{"name":"Bob","age":25,"salary":75000,"dept":"Eng"}');
INSERT INTO json_path_test VALUES (3, '{"name":"Carol","age":35,"salary":120000,"dept":"Eng"}');
COMMIT;

测试1：路径表达式中的DECODE-默认的匹配/结果对

SELECT id, JSON_TRANSFORM(data,
  SET '$.tier' = PATH 'decode($.salary, 50000, "low", 75000, "medium", "high")'
) AS transformed
FROM json_path_test
ORDER BY id;

测试2：没有默认值DECODE-无匹配时返回控制

SELECT id, JSON_TRANSFORM(data,
  SET '$.tier' = PATH 'decode($.salary, 50000, "low", 75000, "medium")'
) AS transformed
FROM json_path_test
ORDER BY id;

测试3：DECODE只有默认值-没有匹配/结果对

SELECT id, JSON_TRANSFORM(data,
  SET '$.label' = PATH 'decode($.salary, "no match")'
) AS transformed
FROM json_path_test
ORDER BY id;

测试4：路径表达式中的CASE-带默认条件的条件/结果对

SELECT id, JSON_TRANSFORM(data,
  SET '$.tier' = PATH 'case($.salary < 60000, "entry", $.salary < 100000, "mid", "senior")'
) AS transformed
FROM json_path_test
ORDER BY id;

测试5：CASE短路求值-第一个为真的条件匹配

SELECT id, JSON_TRANSFORM(data,
  SET '$.tier' = PATH 'case($.salary < 60000, "entry", $.salary < 100000, "mid", "senior")'
) AS transformed
FROM json_path_test
ORDER BY id;

测试6：使用字符串匹配的DECODE

SELECT id, JSON_TRANSFORM(data,
  SET '$.dept_label' = PATH 'decode($.dept, "HR", "Human Resources", "IT", "Information Tech", "Other")'
  ) AS transformed
FROM json_path_test
ORDER BY id;

测试7：实际应用-使用条件式JSON_TRANSFORM更新表

UPDATE json_path_test
SET data = JSON_TRANSFORM(data,
  SET '$.tier' = PATH 'decode($.salary, 50000, "low", 75000, "medium", "high")',
  SET '$.eval' = PATH 'case($.age < 30, "young talent", $.age < 35, "established", "veteran")'
);
COMMIT;
SELECT id, data FROM json_path_test ORDER BY id;

总结

本期对Oracle AI Database 26ai，23.26.2版本新引入的WSQL/JSON路径表达式支持DECODE与CASE新特性进行了完整介绍与实战演示。

老规矩，知道写了些啥。