Oracle 18.4中JSON_TABLE解析无键嵌套JSON数组的NESTED PATH设置

无键数组专用解法 #

对于内部为纯值数组的结构，无需使用通配符，直接将路径指向当前上下文节点本身即可，修改后的查询语句如下：

SELECT tab.id, 
       jt.*
FROM parse_json_array tab,
       json_table(data, '$.dt[*]'
         COLUMNS (NESTED PATH '$.values[*]' COLUMNS(
                     key     PATH '$'  ) 
    )) AS "JT"
where tab.id = 2;

执行后查询id=2的记录可返回正确结果：

ID  KEY
--------
2   a
2   b

兼容两种JSON结构的解法 #

如果需要同时适配带key的对象数组和纯值数组，可借助coalesce函数优先匹配存在的路径，写法如下：

SELECT tab.id, 
       jt.*
FROM parse_json_array tab,
       json_table(data, '$.dt[*]'
         COLUMNS (NESTED PATH '$.values[*]' COLUMNS(
                     key     PATH 'coalesce($.key, $)'  ) 
    )) AS "JT"
where tab.id in (1,2);

执行后会同时返回id=1和id=2的全部4条正确结果：

ID  KEY
--------
1   a
1   b
2   a
2   b

原理解释 #

• 当values数组的元素是对象时，$.key路径存在，会优先读取key字段的值
• 当values数组的元素是纯字符串时，$.key路径不存在返回null，coalesce会自动回退读取当前节点$本身的值
• 该写法完全兼容Oracle 18c及以上版本，适配你使用的XE 18.4版本要求

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