Oracle中当属性值为空时跳过整个JSON_ARRAYAGG数组的问题

问题场景 #

在Oracle中使用JSON_ARRAYAGG函数生成JSON数组时，数组内的对象包含两个属性：硬编码的@type，以及从表中查询的@value。需求是当@value全部为null时，整个JSON数组返回null，但使用absent on null returning blob后未达到预期效果。

原查询语句 #

'field' VALUE
    SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
            '@type' VALUE 'idtype',
            '@value' VALUE tbl.value absent on null returning blob
            ) absent on null returning blob
          )
    from (select * from table tbl) 

预期输出 #

Null

实际输出 #

"field": [
    {
      "@type": "idtype"
    }
  ],

解决办法 #

方案1：过滤空值后聚合 #

在子查询中直接过滤掉value为null的记录，当所有value均为null时，JSON_ARRAYAGG因无数据可聚合会返回null，完全符合需求：

'field' VALUE
    SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                '@type' VALUE 'idtype',
                '@value' VALUE tbl.value ABSENT ON NULL RETURNING BLOB
            ) ABSENT ON NULL RETURNING BLOB
          )
    FROM table tbl
    WHERE tbl.value IS NOT NULL

方案2：用CASE WHEN判断非空值存在性 #

通过EXISTS检查表中是否存在非空的value，仅当存在有效数据时执行聚合操作，否则直接返回null：

'field' VALUE
    CASE
        WHEN EXISTS (SELECT 1 FROM table tbl WHERE tbl.value IS NOT NULL)
        THEN JSON_ARRAYAGG(
            JSON_OBJECT(
                '@type' VALUE 'idtype',
                '@value' VALUE tbl.value ABSENT ON NULL RETURNING BLOB
            ) ABSENT ON NULL RETURNING BLOB
          )
        ELSE NULL
    END
FROM table tbl

原理说明 #

原写法失效的核心原因：

• JSON_OBJECT的ABSENT ON NULL仅会移除值为null的属性（即@value），但仍会生成仅包含@type的对象。
• JSON_ARRAYAGG的ABSENT ON NULL仅当没有任何元素可聚合时才返回null，而原场景中存在生成的对象，因此数组不会为空。

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