Easily identify differences between JSON strings with this simple function.

Create a simple function that compares any two JSON formatted strings and returns the difference in JSON format. The function is especially helpful when logging table inserts, updates, and deletes. The next Blog describes creation of logging and tracking functionality that utilizes this function to only track differences: Cost-Effective Method for Logging Changes in Oracle Tables.

Following is source code for the comparison function with embedded comments describing the code:

CREATE OR REPLACE FUNCTION JSON_String_Diff(     p_JSON_1 IN VARCHAR2
                                                ,p_JSON_2 IN VARCHAR2
                                            ) 
    RETURN VARCHAR2 
IS
  v_JSON_Object_1         JSON_OBJECT_T;                    -- JSON object for first input
  v_JSON_Object_2         JSON_OBJECT_T;                     -- JSON object for second input
  v_JSON_Object_Diff     JSON_OBJECT_T := JSON_OBJECT_T(); -- v_JSON_Object_Diff JSON object for differences
  v_Keys_1                         JSON_KEY_LIST;               -- List of keys from first JSON
  v_Keys_2                         JSON_KEY_LIST;               -- List of keys from second JSON
  v_Value_1                     JSON_ELEMENT_T;           -- Value from first JSON
  v_Value_2                     JSON_ELEMENT_T;           -- Value from second JSON
  v_JSON_String_Diff    Varchar2(32767);
Begin
    Case
    When p_JSON_1 Is Null And p_JSON_2 Is Null Then 
        -- No Difference
        v_JSON_String_Diff    := '[]';
    When p_JSON_1 Is Not Null And p_JSON_2 Is Null Then
        -- Only one parameter populated, so difference is value.
        v_JSON_String_Diff    := p_JSON_1;
    When p_JSON_2 Is Not Null And p_JSON_1 Is Null Then
        -- Only one parameter populated, so difference is value.
        v_JSON_String_Diff    := p_JSON_2;
    Else
      -- Both parameters populated.
         -- Parse the input JSON strings into JSON objects
        v_JSON_Object_1     := JSON_OBJECT_T.PARSE(p_JSON_1);
        v_JSON_Object_2     := JSON_OBJECT_T.PARSE(p_JSON_2);

        -- Get the list of keys from both JSON objects
        v_Keys_1                     := v_JSON_Object_1.GET_KEYS();
        v_Keys_2                     := v_JSON_Object_2.GET_KEYS();

        -- Step 1: Compare keys in the first JSON with the second JSON
        FOR i IN 1..v_Keys_1.COUNT LOOP

          v_Value_1             := v_JSON_Object_1.GET(v_Keys_1(i));  -- Get value for the key from first JSON

          IF v_JSON_Object_2.HAS(v_Keys_1(i)) THEN
            v_Value_2         := v_JSON_Object_2.GET(v_Keys_1(i));  -- Get value for the same key from second JSON
             -- Check if values differ (handle scalar values and string comparison)
            IF NOT v_Value_1.IS_SCALAR OR NOT v_Value_2.IS_SCALAR OR v_Value_1.TO_STRING() != v_Value_2.TO_STRING() THEN
              v_JSON_Object_Diff.PUT(v_Keys_1(i), v_Value_1);  -- Add differing key-value pair to v_JSON_Object_Diff
            END IF;
          ELSE
            -- Key exists in first JSON but not in second, so include it
            v_JSON_Object_Diff.PUT(v_Keys_1(i), v_Value_1);
          END IF;
         END LOOP;

        -- Step 2: Check for keys in the second JSON that are not in the first
        FOR i IN 1..v_Keys_2.COUNT LOOP
          IF NOT v_JSON_Object_1.HAS(v_Keys_2(i)) THEN
            v_Value_2         := v_JSON_Object_2.GET(v_Keys_2(i));  -- Get value for the key from second JSON
            v_JSON_Object_Diff.PUT(v_Keys_2(i), v_Value_2);     -- Add missing key-value pair to v_JSON_Object_Diff
          END IF;
        END LOOP;
        v_JSON_String_Diff    := v_JSON_Object_Diff.TO_STRING();
    End Case;

    -- Return the v_JSON_Object_Diff as a JSON string
    RETURN v_JSON_String_Diff;
Exception
    -- Your error handling

END;
/

If you found this helpful, please hit the like button.