MySQL JSON diff procedure

Question

I'm building a procedure which compares two JSON objects and return two JSON objects with differences between each (compared keys and values). But I have relatively superficial SQL knowledge, so I need your review to avoid any simple mistakes and improve my code.

Compares "first" against "second" and returns the values in "first" that are not present in "second" OR has different value

DELIMITER $$

DROP PROCEDURE IF EXISTS json_diff $$

# Compares "first" against "second" and
# returns the values in "first" that are not present in "second" OR has different value
CREATE PROCEDURE json_diff(IN first JSON, IN second JSON, INOUT firstResult JSON, INOUT secondResult JSON)
BEGIN
    DECLARE oldKey VARCHAR(300);
    DECLARE oldVal, newVal INT;
    DECLARE i, secondHasKey INT DEFAULT 0;
    DECLARE oldKeys, newKeys JSON;

    SELECT JSON_KEYS(first) INTO oldKeys;
    SELECT JSON_KEYS(second) INTO newKeys;

    WHILE i < JSON_LENGTH(oldKeys) DO
        SELECT JSON_EXTRACT(oldKeys, CONCAT('$[',i,']')) INTO oldKey;
        SELECT JSON_EXTRACT(first, CONCAT('$.', oldKey)) INTO oldVal;

        SELECT JSON_CONTAINS_PATH(second, 'one', CONCAT('$.', oldKey)) INTO secondHasKey;

        IF secondHasKey = 1 THEN
            SELECT JSON_EXTRACT(second, CONCAT('$.', oldKey)) INTO newVal;
            IF oldVal != newVal THEN
                SELECT JSON_INSERT(firstResult, CONCAT('$.', oldKey), oldVal) INTO firstResult;
                SELECT JSON_INSERT(secondResult, CONCAT('$.',oldKey), newVal) INTO secondResult;
            end if;
        ELSE
            SELECT JSON_INSERT(firstResult, CONCAT('$.', oldKey), oldVal) INTO firstResult;
        end if;

        SET i = i + 1;
    end while;
END $$

Use above PROCEDURE to make bidirectional comparison.

DELIMITER $$

DROP PROCEDURE IF EXISTS json_diff_both $$

CREATE PROCEDURE json_diff_both(IN first JSON, IN second JSON, INOUT firstResult JSON, INOUT secondResult JSON)
BEGIN
    CALL json_diff(first, second, firstResult, secondResult);
    CALL json_diff(second, first, secondResult, firstResult);
END $$

Answer 1

You are missing a second part of the diff.

I've added the missing part, renamed the variables a bit to make it easier to work with, and added some comments.

DELIMITER $$

DROP PROCEDURE IF EXISTS JSON_OBJECT_DIFF $$

CREATE PROCEDURE JSON_OBJECT_DIFF(IN a JSON, IN b JSON, INOUT aResult JSON, INOUT bResult JSON)
BEGIN
    DECLARE aKeys, bKeys JSON;
    DECLARE currentKey, currentKeyPath TEXT;
    DECLARE aVal, bVal JSON;
    DECLARE aKeysLength, bKeysLength, aHasKey, bHasKey INT;
    DECLARE i INT DEFAULT 0;

    SELECT JSON_KEYS(a) INTO aKeys;
    SELECT JSON_KEYS(b) INTO bKeys;
    SELECT JSON_LENGTH(aKeys) INTO aKeysLength;
    SELECT JSON_LENGTH(bKeys) INTO bKeysLength;

    # Iterate keys in `a` as `currentKey`:
    # * If exists in `b` and !=, insert `a(currentKey)` and `b(currentKey)` into `aResult` and `bResult`
    # * If does not exist in `b`, insert `a(currentKey)` into `aResult`

    WHILE i < aKeysLength DO
        SELECT JSON_EXTRACT(aKeys, CONCAT('$[', i, ']')) INTO currentKey; /* Already quoted */
        SELECT CONCAT('$.', currentKey) INTO currentKeyPath;
        SELECT JSON_EXTRACT(a, currentKeyPath) INTO aVal;

        SELECT JSON_CONTAINS_PATH(b, 'one', currentKeyPath) INTO bHasKey;

        IF bHasKey = 1 THEN
            SELECT JSON_EXTRACT(b, currentKeyPath) INTO bVal;

            IF aVal != bVal THEN
                SELECT JSON_INSERT(aResult, currentKeyPath, aVal) INTO aResult;
                SELECT JSON_INSERT(bResult, currentKeyPath, bVal) INTO bResult;
            end if;
        ELSE
            SELECT JSON_INSERT(aResult, currentKeyPath, aVal) INTO aResult;
        END IF;

        SET i = i + 1;
    END WHILE;

    # Iterate keys in `b` as `currentKey`:
    # * If does not exist in `a`, insert `b(currentKey)` into `bResult`

    SET i = 0;
    WHILE i < bKeysLength DO
        SELECT JSON_EXTRACT(bKeys, CONCAT('$[', i, ']')) INTO currentKey; /* Already quoted */
        SELECT CONCAT('$.', currentKey) INTO currentKeyPath;

        SELECT JSON_CONTAINS_PATH(a, 'one', currentKeyPath) INTO aHasKey;

        IF aHasKey = 0 THEN
            SELECT JSON_EXTRACT(b, currentKeyPath) INTO bVal;
            SELECT JSON_INSERT(bResult, currentKeyPath, bVal) INTO bResult;
        END IF;

        SET i = i + 1;
    END WHILE;
END $$