Oracle使用JSON_QUERY提取符合条件JSON数组元素返回null问题

原SQL返回null、无法提取目标元素的核心原因有两点：

• JSON_QUERY 默认仅支持返回单个JSON节点，使用[*]通配符匹配多个数组元素时，如果不添加WITH WRAPPER子句封装返回结果，会直接返回null，这也是指定具体数组下标（单节点）能正常返回、用通配符就返回null的直接原因。
• 原SQL的j.AUS_FLAG ='Y'过滤条件仅能控制最终返回的行，不会对JSON_QUERY提取的DIRECTOR数组做元素级过滤，哪怕过滤生效，返回的也是DIRECTOR全量数组，不符合提取匹配元素的需求。

方案1：JSON_TABLE返回匹配元素（兼容性最好，支持Oracle 12cR1及以上版本） #

通过JSON_TABLE遍历DIRECTOR数组时，直接定义列存储当前遍历到的完整数组元素，再通过AUS_FLAG字段过滤即可拿到符合要求的元素：

SELECT 
  j.director_element AS target_director_data
FROM TB_COP_BUSS_OBJ_TXN FD,
JSON_TABLE(
  FD.OBJECT_DATA,
  '$.AOF.LEAD_DATA.DIRECTOR[*]' 
  COLUMNS (
    AUS_FLAG VARCHAR2(40) PATH '$.CHECKBOX.AUS_FLAG.value',
    director_element FORMAT JSON PATH '$' -- 加FORMAT JSON避免JSON内容被转义为普通字符串
  )
) j
WHERE FD.OBJECT_PRI_KEY_1 = 'XXXXXXX' 
  AND j.AUS_FLAG = 'Y';

如果需要把所有符合条件的元素合并为一个JSON数组返回，可以套一层JSON_ARRAYAGG聚合函数：

SELECT 
  JSON_ARRAYAGG(j.director_element FORMAT JSON) AS target_director_list
FROM TB_COP_BUSS_OBJ_TXN FD,
JSON_TABLE(
  FD.OBJECT_DATA,
  '$.AOF.LEAD_DATA.DIRECTOR[*]' 
  COLUMNS (
    AUS_FLAG VARCHAR2(40) PATH '$.CHECKBOX.AUS_FLAG.value',
    director_element FORMAT JSON PATH '$'
  )
) j
WHERE FD.OBJECT_PRI_KEY_1 = 'XXXXXXX' 
  AND j.AUS_FLAG = 'Y';

方案2：JSON路径内联过滤（写法更简洁，支持Oracle 12cR2及以上版本） #

高版本Oracle支持在JSON路径中直接写过滤谓词，不需要关联JSON_TABLE即可直接筛选符合条件的数组元素，注意必须加WITH ARRAY WRAPPER避免多值返回null：

SELECT JSON_QUERY(
  OBJECT_DATA,
  '$.AOF.LEAD_DATA.DIRECTOR[*]?(@.CHECKBOX.AUS_FLAG.value == "Y")'
  WITH ARRAY WRAPPER
  -- 如果不需要空数组返回，可以加NULL ON EMPTY适配空结果场景
) AS target_director_list
FROM TB_COP_BUSS_OBJ_TXN FD
WHERE FD.OBJECT_PRI_KEY_1 = 'XXXXXXX';

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