Oracle 19c - Remove duplicates inside a JSON array

In Oracle 19c with Tables having JSON columns, we ended up having duplicates within a JSON array due to an erroneous background process (I formatted the JSON with new lines for readability):

{"emp_dtls":[ {"emp_id":"1159221795_1","SNo":"1159221801_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"APPROVE","days":8.0,"joinDt":"07/25/2023"},   {"emp_id":"992070135_1","SNo":"1159221314_5","SPID":"987149884_1","PPID":"1045588383_1","empType":"CANCEL","days":5.0,"joinDt":"07/25/2023"},  {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"}, {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"}, {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"}, {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},  {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1027804255_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},  {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},   {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},  {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"APPROVE","days":5.0,"joinDt":"07/25/2023"}]}

In the above JSON, the below data is there 6 times within the same array:

 {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"}

Is there a way to keep one out of the six and remove the other five without having to split them into columns and then use a JSON array aggregate function after eliminating duplicate rows?

Solution

Split the array into individual items (as JSON, not columns, assuming that your key-values pairs are in a consistent order; if they aren't then you'll have to split the data into columns) and then use DISTINCT on that and re-aggregate:

SELECT JSON_OBJECT(
         KEY 'emp_dtls' VALUE JSON_ARRAYAGG(detail FORMAT JSON)
       ) AS value
FROM   (
  SELECT DISTINCT j.detail
  FROM   table_name t
         CROSS APPLY JSON_TABLE(
           t.value,
           '$.emp_dtls[*]'
           COLUMNS
             detail VARCHAR2(4000) FORMAT JSON PATH '$'
         ) j
)

Which, for the sample data:

create table table_name
(
  value CLOB CHECK (value IS JSON)
);

INSERT INTO table_name (value)
SELECT '{"emp_dtls":[ {"emp_id":"1159221795_1","SNo":"1159221801_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"APPROVE","days":8.0,"joinDt":"07/25/2023"},   {"emp_id":"992070135_1","SNo":"1159221314_5","SPID":"987149884_1","PPID":"1045588383_1","empType":"CANCEL","days":5.0,"joinDt":"07/25/2023"},  {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"}, {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"}, {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"}, {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},  {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1027804255_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},
    {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},  {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},   {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},  {"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"APPROVE","days":5.0,"joinDt":"07/25/2023"}]}' FROM DUAL;

Outputs:

VALUE
{"emp_dtls":[{"emp_id":"1159221795_1","SNo":"1159221801_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"APPROVE","days":8.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"1159221314_5","SPID":"987149884_1","PPID":"1045588383_1","empType":"CANCEL","days":5.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1027804255_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"APPROVE","days":5.0,"joinDt":"07/25/2023"}]}

VALUE

{"emp_dtls":[{"emp_id":"1159221795_1","SNo":"1159221801_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"APPROVE","days":8.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"1159221314_5","SPID":"987149884_1","PPID":"1045588383_1","empType":"CANCEL","days":5.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1045588383_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"1027804255_1","empType":"MAPPINGCHANGE","days":5.0,"joinDt":"07/25/2023"},{"emp_id":"992070135_1","SNo":"992070162_1","SPID":"987149884_1","PPID":"920104723_1","empType":"APPROVE","days":5.0,"joinDt":"07/25/2023"}]}

fiddle