Oracle 19c如何从CLOB格式JSON的未知路径提取指定键值

要提取JSON结构中任意位置的指定键（比如name）的值，Oracle 19c中JSON_TABLE是最适合的工具——JSON_VALUE/JSON_QUERY默认仅返回第一个匹配结果，而JSON_TABLE可以把所有匹配的键值展开为行数据，完全满足你“不知道确切路径，只按键名提取”的需求。

示例SQL实现 #

假设你的表名为json_data_table，存储JSON的CLOB列名为json_content，执行以下SQL即可获取所有name键的值：

SELECT jt.name_val
FROM json_data_table,
     JSON_TABLE(
         json_content,
         '$..name' COLUMNS (
             name_val VARCHAR2(100) PATH '$'
         )
     ) jt;

代码说明 #

• $..name：JSON路径表达式，..表示递归遍历JSON的所有层级，匹配所有名为name的键；
• JSON_TABLE函数将每个匹配的name值映射为单独一行，PATH '$'表示取当前匹配键对应的具体值；
• 针对你提供的示例JSON，执行后会返回两行结果：John Doe和Jane Doe。

扩展：获取值的来源路径 #

如果需要同时知道每个值在JSON中的完整路径，可以在JSON_TABLE中额外添加路径列：

SELECT jt.name_val, jt.value_path
FROM json_data_table,
     JSON_TABLE(
         json_content,
         '$..name' COLUMNS (
             name_val VARCHAR2(100) PATH '$',
             value_path VARCHAR2(200) PATH '$.'
         )
     ) jt;

执行后会返回：

内容的提问来源于stack exchange，提问作者Cavid Haciyev