Oracle 19c CLOB提取JSON元素及查询空值问题解决方案

一、检测含"location":{}的坏记录 #

通过JSON函数定位EntitlementJSON数组中存在空对象location的记录，可实现批量标记或提取具体异常角色：

1. 批量标记记录状态 #

直接判断单条记录是否存在异常location：

SELECT
  employeeID,
  CASE
    WHEN JSON_EXISTS(JSON_DATA, '$.EntitlementJSON[*]?(@.location == {})') THEN '坏记录'
    ELSE '正常'
  END AS record_status
FROM TEST_JSON;

说明：JSON_EXISTS通过JSON路径表达式匹配数组中任意元素的location为空对象的情况，快速标记异常记录。

2. 提取具体异常角色 #

若需定位到具体的异常角色信息，可结合JSON_TABLE展开数组后过滤：

SELECT
  t.employeeID,
  j.roleName,
  'location为空对象' AS error_detail
FROM TEST_JSON t,
     JSON_TABLE(t.JSON_DATA, '$.EntitlementJSON[*]'
       COLUMNS
         roleName VARCHAR2(100) PATH '$.roleName',
         location CLOB PATH '$.location'
     ) j
WHERE JSON_VALUE(j.location, '$.type()') = 'object' 
  AND JSON_VALUE(j.location, '$.size()') = 0;

说明：通过JSON_VALUE获取location的类型和元素数量，精准筛选出空对象格式的异常项。

二、修复查询时的location空值异常 #

问题根源是当同角色存在location: {}的空对象时，JSON_TABLE解析空对象会导致后续聚合（如LISTAGG）返回null。通过将空对象转换为空数组，确保解析逻辑统一：

修复后的查询语句 #

SELECT
  t.employeeID,
  j.roleName,
  NVL(LISTAGG(l.loc_value, ', ') WITHIN GROUP (ORDER BY l.loc_value), '') AS locations
FROM TEST_JSON t,
     JSON_TABLE(t.JSON_DATA, '$.EntitlementJSON[*]'
       COLUMNS
         roleName VARCHAR2(100) PATH '$.roleName',
         location CLOB PATH '$.location'
     ) j,
     JSON_TABLE(
       -- 将空对象location转换为空数组，保证解析一致性
       CASE 
         WHEN JSON_VALUE(j.location, '$.type()') = 'object' AND JSON_VALUE(j.location, '$.size()') = 0 
         THEN '[]' 
         ELSE j.location 
       END,
       '$[*]' COLUMNS loc_value VARCHAR2(100) PATH '$'
     ) l
WHERE t.employeeID = 2
GROUP BY t.employeeID, j.roleName;

说明：

1. 用CASE语句判断空对象格式的location，将其转换为空数组'[]'；
2. 统一后的数组格式可被JSON_TABLE正常解析，避免因空对象导致的解析失败；
3. 配合NVL确保无有效location时返回空字符串而非null。

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