removing identical objects from json array-CodePudding

I have two json arrays with objects, i want to combine them into one without duplicates.

[{"id":111111,"name":"name1"},{"id":222222,"name":"name2"},{"id":555555,"name":"name5"}]
[{"id":111111,"name":"name1"},{"id":222222,"name":"name2"},{"id":333333,"name":"name3"{"id":444444,"name":"name4"}]

This is what i have at the moment

function compare(j1 json_object_t,j2 json_object_t) return boolean
is
  j1_keys json_key_list;
  j2_keys json_key_list;
begin
  j1_keys := j1.get_keys;
  j2_keys := j2.get_keys;
if j1_keys.count != j2_keys.count then return false; end if;
for i in .. j2_keys.count loop 
  for j in .. j2_keys.count loop
    if j1_keys(j) != j2_keys(i) then return false; end if;
   end loop;
return true;
end loop;
end;

<<loop>>
for i in 0 .. object1.get_size - 1 loop
  for j in 0 .. object2.get_size - 1 loop
    if compare(object1,object2) = true then
      continue <loop>;
    end if;
  end loop;
  array.append(object_1);
end loop;

Im getting the right amount of objects in the array but whats in the array is wrong. instead of desired result

[{"id":111111,"name":"name1"},{"id":222222,"name":"name2"},{"id":555555,"name":"name5"}{"id":333333,"name":"name3"},{"id":444444,"name":"name4"}]

im getting

[{"id":111111,"name":"name1"},{"id":222222,"name":"name2"},{"id":55555,"name":"name5"},{"id":333333,"name":"name3"},{"id":333333,"name":"name3"}]

thanks

CodePudding user response：

You can use:

DECLARE 
  arr1 JSON_ARRAY_T := JSON_ARRAY_T.PARSE(
    '[{"id":111111,"name":"name1"},{"id":222222,"name":"name2"},{"id":555555,"name":"name5"}]'
  );
  arr2 JSON_ARRAY_T := JSON_ARRAY_T.PARSE(
    '[{"id":111111,"name":"name1"},{"id":222222,"name":"name2"},{"id":333333,"name":"name3"},{"id":444444,"name":"name4"}]'
  );
  
  FUNCTION compare_element(
    j1 json_element_t,
    j2 json_element_t
  ) RETURN BOOLEAN;

  FUNCTION compare_array(
    j1 json_array_t,
    j2 json_array_t
  ) RETURN BOOLEAN
  IS
  BEGIN
    IF j1.get_size <> j2.get_size
    THEN
      RETURN FALSE;
    END IF;
    
    FOR i IN 0 .. j1.get_size
    LOOP
      IF NOT compare_element(j1.get(i), j2.get(i))
      THEN
        RETURN FALSE;
      END IF;
    END LOOP;
    RETURN TRUE;
  END;
  
  FUNCTION compare_object(
    j1 json_object_t,
    j2 json_object_t
  ) return boolean
  IS
    j1_keys json_key_list := j1.get_keys;
    j2_keys json_key_list := j2.get_keys;
    key     VARCHAR2(4000);
  BEGIN
    IF j1_keys.COUNT != j2_keys.COUNT
    THEN
      RETURN FALSE;
    END IF;
    
    FOR i IN 1 .. j2_keys.COUNT
    LOOP
      key := j2_keys(i);

      IF NOT j1.has(key)
      OR NOT compare_element(j1.get(key), j2.get(key))
      THEN
        RETURN FALSE;
      END IF;
    END LOOP;
    RETURN TRUE;
  END;

  FUNCTION compare_element(
    j1 json_element_t,
    j2 json_element_t
  ) RETURN BOOLEAN
  IS
  BEGIN
    RETURN (j1.is_object()    AND j2.is_object()    AND compare_object(TREAT(j1 AS JSON_OBJECT_T), TREAT(j2 AS JSON_OBJECT_T)))
        OR (j1.is_array()     AND j2.is_array()     AND compare_array(TREAT(j1 AS JSON_ARRAY_T), TREAT(j2 AS JSON_ARRAY_T)))
        OR (j1.is_true()      AND j2.is_true())
        OR (j1.is_false()     AND j2.is_false())
        OR (j1.is_null()      AND j2.is_null())
        OR (j1.is_timestamp() AND j2.is_timestamp() AND j1.to_timestamp() = j2.to_timestamp())
        OR (j1.is_date()      AND j2.is_date()      AND j1.to_date() = j2.to_date())
        OR (j1.is_number()    AND j2.is_number()    AND j1.to_number() = j2.to_number())
        OR (j1.is_string()    AND j2.is_string()    AND j1.to_string() = j2.to_string());
  END;
  
BEGIN
  <<outer_loop>>
  FOR i IN 0 .. arr2.get_Size - 1
  LOOP
    FOR j IN 0 .. arr1.get_size - 1
    LOOP
      IF compare_element(arr1.get(j), arr2.get(i))
      THEN
        CONTINUE outer_loop;
      END IF;
    END LOOP;
    arr1.append(arr2.get(i));
  END LOOP;

  FOR i IN 0 .. arr1.get_Size - 1
  LOOP
    DBMS_OUTPUT.PUT_LINE(arr1.get(i).to_string());
  END LOOP;
END;
/

Which outputs:

{"id":111111,"name":"name1"}
{"id":222222,"name":"name2"}
{"id":555555,"name":"name5"}
{"id":333333,"name":"name3"}
{"id":444444,"name":"name4"}

db<>fiddle here

CodePudding user response：

If your JSON data is stored in tables (or even if it isn't), you can use SQL to get the DISTINCT values using JSON_TABLE, then combine them back into an array of JSON objects.

If you did want to sort your array by "id" or "name", it would be very easy with this method as well since you can just add an ORDER BY clause. This method also makes it very simple if you wanted to combine more than 2 arrays because all you need to do is select additional "rows" from your table.

WITH
    sample_table (json_data)
    AS
        (SELECT '[{"id":111111,"name":"name1"},{"id":222222,"name":"name2"},{"id":555555,"name":"name5"}]'
           FROM DUAL
         UNION ALL
         SELECT '[{"id":111111,"name":"name1"},{"id":222222,"name":"name2"},{"id":333333,"name":"name3"},{"id":444444,"name":"name4"}]'
           FROM DUAL)
SELECT json_arrayagg (json_object ('id' VALUE id, 'name' VALUE name))     AS unique_objects
  FROM (SELECT DISTINCT id, name
          FROM sample_table  s
               CROSS JOIN
               JSON_TABLE (s.json_data, '$[*]' COLUMNS id PATH '$.id', name PATH '$.name'));



                                                                                                                                                 UNIQUE_OBJECTS
_______________________________________________________________________________________________________________________________________________________________
[{"id":"111111","name":"name1"},{"id":"333333","name":"name3"},{"id":"555555","name":"name5"},{"id":"222222","name":"name2"},{"id":"444444","name":"name4"}]