Oracle数据库JSON列添加ABSENT ON NULL选项去除空值的技术方案咨询

你遇到的问题很典型——直接用json_object传入整个JSON字符串是行不通的，因为json_object需要的是显式的键值对定义（比如KEY 'REC_TYPE_IND' VALUE '1'），而不是一个完整的JSON文本。下面给你两种可行的解决方案，分别适用于不同版本的Oracle：

方法一：用JSON_TRANSFORM（Oracle 19c及以上推荐） #

如果你的Oracle版本是19c或更高，JSON_TRANSFORM是最简洁的方式，它可以直接修改JSON，移除所有值为null的键：

SELECT 
  json_transform(
    json_data,
    remove '$.key()?(@ == null)'  -- 匹配所有值为null的键并移除
    RETURNING VARCHAR2(4000)
  ) AS trimmed_json
FROM your_old_table;

这个语句的逻辑是：通过路径表达式$.key()?(@ == null)匹配JSON中所有值为null的键，然后用remove操作把它们删掉，最后返回精简后的JSON字符串。

方法二：用JSON_TABLE + JSON_OBJECTAGG（兼容旧版本） #

如果你的Oracle版本低于19c，可以先把原JSON拆解成键值对，再重新聚合并应用ABSENT ON NULL：

SELECT 
  t.id,  -- 原表的主键或唯一标识，用来分组聚合回单条记录的JSON
  json_objectagg(
    key jt.key_name value jt.value_data
    ABSENT ON NULL  -- 自动排除值为null的键
    RETURNING VARCHAR2(4000)
  ) AS trimmed_json
FROM your_old_table t,
     json_table(
       t.json_data,
       '$.*' columns (
         key_name VARCHAR2(100) PATH '$.key',  -- 提取JSON的键名
         value_data JSON PATH '$.value'        -- 提取对应的值（保持JSON类型）
       )
     ) jt
GROUP BY t.id;

步骤说明： #

1. JSON_TABLE把原JSON的每个键值对拆成一行数据，每个行包含键名key_name和对应的值value_data；
2. JSON_OBJECTAGG将这些键值对重新聚合成一个JSON对象，同时通过ABSENT ON NULL自动过滤掉值为null的键；
3. 最后用GROUP BY按原表的主键分组，确保每条原始记录对应一个精简后的JSON。

插入到新表 #

不管用哪种方法，你都可以把处理后的结果直接插入新表：

INSERT INTO your_new_table (id, json_data)
SELECT id, trimmed_json
FROM (
  -- 这里放入上面任意一种方法的SELECT语句
);

为什么你之前的语句报错？ #

你尝试的SELECT json_object (json_query(json_data,'$') ABSENT ON NULL ...)会报ORA-02000: missing VALUE keyword，是因为json_object的语法要求每个键都必须搭配VALUE指定对应的值，比如：

json_object(
  'REC_TYPE_IND' VALUE '1',
  'ID' VALUE '1234',
  ABSENT ON NULL
)

而你直接传入了一个完整的JSON字符串，不符合json_object的参数格式，所以报错。

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