The Ultimate Guide to High Availability with MariaDB

Comparing arrays

Hello, please help my, i have two json arrays and i need to know if any element of one array exists in another attar in another words i need to compare this arrays.

Mysql has such function - JSON_OVERLAPS https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-overlaps

Do the mariadb have such function or any alternative to do it?

Now to do it i need multiple times use json_contains and OR operator between them.

Thanks.

Answer Answered by hellman hellman in this comment.

I've wrote small function to do it, but may be exists better solution ?

 CREATE OR REPLACE FUNCTION in_array(
        in_array1 BLOB,
        in_array2 BLOB        
    ) RETURNS bool DETERMINISTIC
BEGIN
    DECLARE i INT UNSIGNED
        DEFAULT 0;
    DECLARE v_count INT UNSIGNED
        DEFAULT JSON_LENGTH(in_array2);
    DECLARE ret bool DEFAULT 0;

    WHILE i < v_count DO

        SET ret := JSON_CONTAINS(in_array1, JSON_EXTRACT(in_array2, CONCAT('$[', i, ']')));

        IF ret = 1 THEN
        return 1;
        END IF;
        SET i := i + 1;
    END WHILE;

    return 0;
END

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.