SQL Function to extract a field + subfield

DELIMITER $$

CREATE or replace FUNCTION GetSubfieldValuesAsJson(xmlData TEXT, tagName VARCHAR(255), subfieldCode VARCHAR(255))
RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE result TEXT DEFAULT '[';
    DECLARE subfieldValue TEXT DEFAULT '';
    DECLARE i INT DEFAULT 1;
    DECLARE hasNext BOOLEAN DEFAULT TRUE;

    -- Check if xmlData is not a valid XML
    IF ExtractValue(xmlData, '/*') = '' THEN
        RETURN '[]';
    END IF;

    WHILE hasNext DO
        SET subfieldValue = ExtractValue(xmlData, CONCAT('//datafield[@tag="', tagName, '"]/subfield[@code="', subfieldCode, '"][', i, ']'));
        IF subfieldValue = '' THEN
            -- If it's the first iteration and no result, return empty JSON array
            IF i = 1 THEN
                RETURN '[]';
            END IF;
            SET hasNext = FALSE;
        ELSE
            SET result = CONCAT(result, IF(i = 1, '', ','), '"', REPLACE(subfieldValue, '"', '\"'), '"');
            SET i = i + 1;
        END IF;
    END WHILE;

    RETURN CONCAT(result, ']');
END$$

DELIMITER ;

with cte as ( SELECT metadata, biblionumber,  GetSubfieldValuesAsJson(metadata, '942', 'c') json_942_c FROM biblio_metadata)  select biblionumber, json_942_c from cte where json_contains(json_942_c, '"CR"');