Oracle中提取JSON路径所有去重值失败，如何排查问题？

你遇到的问题出在函数的使用场景上：JSON_VALUE和JSON_QUERY默认仅返回路径匹配到的第一个元素，无法遍历整个reviews数组并提取所有rating值，这就是你漏掉2、3、8等值的原因。

要提取数组中所有rating并去重，必须用JSON_TABLE将JSON数组展开为关系型行数据，再做去重处理。

假设你的表名为products，正确的SQL语句如下：

SELECT DISTINCT jt.rating
FROM products,
     JSON_TABLE(
       product_details,
       '$.reviews[*]' COLUMNS (
         rating NUMBER PATH '$.rating'
       )
     ) jt
WHERE jt.rating IS NOT NULL;

关键说明： #

• JSON_TABLE会把reviews数组的每个元素拆成独立行，每行提取对应的rating字段
• DISTINCT用于过滤重复的rating值
• 若不需要过滤空值，可去掉WHERE jt.rating IS NOT NULL条件

另外补充两个函数的适用场景：

• JSON_VALUE：仅用于提取单个JSON标量值，路径匹配多个元素时只返回第一个
• JSON_QUERY：用于提取JSON片段（如子数组、对象），同样默认只返回第一个匹配的片段，无法展开整个数组

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