postgresql自定义函数实现功能有两个数组arr1,arr2,返回第一个数组中不在第二个数组的数据

CREATE OR REPLACE FUNCTION array_difference(
    arr1 text[], 
    arr2 text[]
)
RETURNS text[] AS
$$
DECLARE
    result_arr text[];
BEGIN
    -- 初始化结果数组为一个空数组
    result_arr := '{}';

    -- 遍历第一个数组中的每个元素
    FOR i IN 1..array_length(arr1, 1) LOOP
        -- 如果当前元素不在第二个数组中，则将其添加到结果数组中
        IF arr1[i] <> ALL(arr2) THEN
            result_arr := array_append(result_arr, arr1[i]);
        END IF;
    END LOOP;

    RETURN result_arr;
END;
$$
LANGUAGE plpgsql;


-- 示例数据
CREATE TEMP TABLE example_data (
    arr1 text[],
    arr2 text[]
);

INSERT INTO example_data VALUES
    ('{apple, banana, orange}', '{banana, kiwi, mango}'),
    ('{red, green, blue}', '{yellow, blue, purple}');

-- 调用函数
SELECT array_difference(arr1, arr2) AS result FROM example_data;


result
--------------------
{apple,orange}
{red,green}

-- 实战
SELECT array_difference((SELECT array(select json_array_elements_text('{"users":["1685947296799670275","1685947296799670274"],"roles":[],"groups":[],"organizations":[]}'::json->'users'))),
 (SELECT array(select json_array_elements_text('{"users":["1685947296799670275","1685947296799670276"],"roles":[],"groups":[],"organizations":[]}'::json->'users'))))