Oracle 19c任意JSON数据中指定属性统一对象查询方案

需求说明 #

在Oracle 19c环境中，需查询某表JSON列内是否存在同时包含id、type、text、call属性，且满足type='指定值'、text='指定值'、call='指定值'的对象——无论该对象是根对象，还是嵌套在任意层级的对象/数组中。

原尝试的JSON_EXISTS语句存在两类问题：

• 匹配到不同对象的属性组合（跨对象满足条件），无法保证所有条件属于同一个对象
• 需要明确指定JSON路径，无法适配结构未知、层级不确定的任意JSON数据

同时需了解Oracle 21c是否原生支持该需求（当前无法升级版本）。

Oracle 19c 实现方案 #

19c中无原生的任意层级同对象匹配语法，可通过递归遍历所有JSON节点+属性提取的方式实现，无需固定路径，覆盖所有嵌套场景。

完整测试代码 #

WITH Data AS (
    SELECT '1' AS id, '{id:"1",type:"menu",text:"option1",call:"option1()"}' AS json FROM DUAL   UNION ALL
    SELECT '2-onlySubElements' AS id, 
        '{id:"2a",type:"menu",text:"option2",call:"option2()", 
            subElements:[
                 {id:"2.1",type:"menu",text:"option2.1",call:"option21()", 
                    subElements:[{id:"2.1.1",type:"menu",text:"option2.1.1",call:"option211()"} ]},
                 {id:"2.2",type:"menu",text:"option2.2",call:"option22()"}    
            ]
          }' AS json FROM DUAL   UNION ALL
    SELECT '2b-mixOfInnerElements' AS id, 
        '{id:"2b",type:"menu",text:"option3",call:"option3()", 
            subElements:[         
                {id:"2.1",type:"menu",text:"option2.1",call:"option21()", 
                    innerElements:[{id:"2.1.1",type:"menu",text:"option2.1.1",call:"option211()"} ]},        
                {id:"2.2",type:"menu",text:"option2.2",call:"option22()"}
            ]  
          }' AS json FROM DUAL   UNION ALL
    SELECT '0' AS id, '{id:"0",type:"label",text:"label0"}' AS json FROM DUAL
),
-- 递归遍历所有JSON节点（对象、数组）
RecursiveJson AS (
    SELECT 
        d.id AS data_id,
        d.json,
        jt.obj,
        1 AS depth
    FROM Data d,
         JSON_TABLE(d.json, '$' COLUMNS (obj JSON PATH '$')) jt
    UNION ALL
    SELECT 
        r.data_id,
        r.json,
        jt.obj,
        r.depth + 1 AS depth
    FROM RecursiveJson r,
         JSON_TABLE(r.obj, '$.*' COLUMNS (
             obj JSON PATH '$'
         )) jt
    WHERE JSON_TYPE(jt.obj) IN ('OBJECT', 'ARRAY')
),
-- 提取所有对象的目标属性
ObjectProps AS (
    SELECT 
        data_id,
        json,
        JSON_VALUE(obj, '$.id') AS obj_id,
        JSON_VALUE(obj, '$.type') AS obj_type,
        JSON_VALUE(obj, '$.text') AS obj_text,
        JSON_VALUE(obj, '$.call') AS obj_call
    FROM RecursiveJson
    WHERE JSON_TYPE(obj) = 'OBJECT' -- 仅处理对象类型节点
)
-- 筛选符合条件的记录（确保所有属性属于同一对象）
SELECT DISTINCT
    rownum,
    JSON_VALUE(json, '$.type') AS root_type,
    data_id,
    json
FROM ObjectProps
WHERE 
    obj_id IS NOT NULL 
    AND obj_type = 'menu' 
    AND obj_text = 'option2.1.1' -- 替换为目标text值
    AND obj_call = 'option211()'; -- 替换为目标call值

代码逻辑说明 #

1. 递归CTE RecursiveJson：遍历JSON的所有层级节点，包括根对象、嵌套对象和数组，将每个节点单独提取
2. ObjectProps CTE：从所有节点中筛选出对象类型，提取id、type、text、call四个目标属性
3. 最终查询：筛选出同时满足所有属性条件的对象，通过DISTINCT避免同一条JSON数据被多次匹配

Oracle 21c 原生支持情况 #

Oracle 21c增强了JSON路径的递归下降能力，可直接通过JSON_EXISTS实现任意层级的同对象匹配，无需手动递归：

SELECT *
FROM Data
WHERE JSON_EXISTS(
    json,
    '$..*?(@.id exists() && @.type == "menu" && @.text == "option2.1.1" && @.call == "option211()")'
);

其中$..*表示遍历所有层级的节点，?()内的条件确保所有属性都属于同一个对象，完美适配结构未知的JSON数据。

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