Oracle 19c如何去除JSON数组内的重复元素？

可以实现，无需手动将数组拆分到持久化列，通过Oracle 19c的JSON函数能在单条SQL中完成去重并生成无重复元素的JSON数组，以下是具体实现方案：

1. 查询生成去重后的JSON #

假设数据表名为emp_table，存储JSON的列名为emp_json，执行以下SQL可直接获取去重后的完整JSON结构：

SELECT 
  JSON_OBJECT(
    'emp_dtls' VALUE JSON_ARRAYAGG(DISTINCT emp_obj FORMAT JSON)
  ) AS cleaned_json
FROM 
  emp_table,
  JSON_TABLE(
    emp_json,
    '$.emp_dtls[*]' COLUMNS emp_obj JSON PATH '.'
  ) jt;

逻辑说明： #

• JSON_TABLE临时展开emp_dtls数组中的每个元素为独立JSON对象
• DISTINCT对完全重复的JSON对象去重（Oracle会严格比对整个JSON的键值对及顺序）
• JSON_ARRAYAGG将去重后的对象重新聚合成数组
• JSON_OBJECT将数组包装回原JSON结构

2. 直接更新原表的JSON列 #

如果需要修正原表中的重复数据，可使用UPDATE语句：

UPDATE emp_table
SET emp_json = (
  SELECT 
    JSON_OBJECT(
      'emp_dtls' VALUE JSON_ARRAYAGG(DISTINCT emp_obj FORMAT JSON)
    )
  FROM 
    JSON_TABLE(
      emp_table.emp_json,
      '$.emp_dtls[*]' COLUMNS emp_obj JSON PATH '.'
    ) jt
);

3. 基于指定字段去重（可选） #

如果不需要基于整个JSON对象去重，而是指定字段（比如emp_id+SNo），可调整SQL：

SELECT 
  JSON_OBJECT(
    'emp_dtls' VALUE JSON_ARRAYAGG(emp_obj FORMAT JSON)
  ) AS cleaned_json
FROM (
  SELECT DISTINCT 
    emp_id, SNo, emp_obj
  FROM 
    emp_table,
    JSON_TABLE(
      emp_json,
      '$.emp_dtls[*]' COLUMNS 
        emp_id VARCHAR2(50) PATH '$.emp_id',
        SNo VARCHAR2(50) PATH '$.SNo',
        emp_obj JSON PATH '.'
    ) jt
);

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