SQL function to remove duplicate entries from a JSON array. Based on MYSQL 5.7+

json_uniq.sql

CREATE FUNCTION JSON_UNIQ(arr JSON) RETURNS json
BEGIN

	SET @arr = arr;
	SET @a_length = JSON_LENGTH(@arr);
	SET @loop_index = @a_length;

	WHILE @loop_index >= 0 DO

		SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));   
		SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);

		IF @itemcount > 1 THEN
			SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
			SET @loop_index = @loop_index - 1;
		END IF;

		SET @loop_index = @loop_index - 1;

	End WHILE;

	RETURN CAST(@arr AS JSON);
    
END

Hi Dave, it doesn't work well in my newest navicat

Works flawlessly for me. Thanks.

SELECT JSON_UNIQ('["1", "2", "3", "2"]')

this one dedupes null values as well

CREATE FUNCTION JSON_UNIQ(arr JSON) RETURNS JSON DETERMINISTIC
BEGIN

	SET @arr = arr;
	SET @a_length = JSON_LENGTH(@arr);
	SET @loop_index = @a_length;

	WHILE @loop_index >= 0 DO

		SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));   
		SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);

		IF @itemcount > 1 OR @item = 'null' THEN
			SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
		END IF;

		SET @loop_index = @loop_index - 1;

	End WHILE;

	RETURN CAST(@arr AS JSON);
    
END

Just note that JSON_SEARCH currently only works on strings so this will not support numeric arrays. So store numeric as strings like the above example to make this work. That got me until I figured this out.

Since I got the error

"ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)"

when creating the function, and also not all duplicates were removed but only one, I asked ChatGPT. Here is his answer:

CREATE FUNCTION JSON_UNIQ(arr JSON) RETURNS json READS SQL DATA
BEGIN
    DECLARE tempArr JSON DEFAULT JSON_ARRAY();
    DECLARE tempItem JSON DEFAULT NULL;
    DECLARE tempIndex INT DEFAULT NULL;
    DECLARE i INT DEFAULT 0;
    
    WHILE i < JSON_LENGTH(arr) DO
        SET tempItem = JSON_EXTRACT(arr, CONCAT('$[', i, ']'));

        IF NOT JSON_CONTAINS(tempArr, tempItem) THEN
            SET tempArr = JSON_INSERT(tempArr, CONCAT('$[', JSON_LENGTH(tempArr), ']'), tempItem);
        END IF;

        SET i = i + 1;
    END WHILE;

    RETURN tempArr;
END;