[SOLVED] Replacing values in a string using JSON (MySQL/MariaDB)

Issue

I have tried, unsuccessfully, a way to use the values of a column containing a JSON to replace segments of a string using only SQL query capabilities.

Below is some example data:

MariaDB [test_db]> SELECT * FROM events_log LIMIT 3;
+----+-------------------------------+----------------------+-----------------+----------------+-----------------+-----------+---------------------+------+
| id | textMessage                   | textMessageVariables | referenceColumn | referenceValue | referenceSource | createdBy | createdAt           | meta |
+----+-------------------------------+----------------------+-----------------+----------------+-----------------+-----------+---------------------+------+
|  1 | Data added (#%referenceCode%) | {"%referenceCode%":254}       | id              | 1              | surveys  |         1 | 2022-02-14 15:41:58 | {}   |
|  2 | Data added (#%referenceCode%) | {"%referenceCode%":354}       | id              | 2              | surveys  |         1 | 2022-02-14 15:42:09 | {}   |
|  3 | Data added (#%referenceCode%) | {"%referenceCode%":687}       | id              | 3              | surveys  |         5 | 2022-02-15 09:42:36 | {}   |
+----+-------------------------------+----------------------+-----------------+----------------+-----------------+-----------+---------------------+------+

The desired result would be to be able to replace the %referenceCode% value using the respective JSON value in the textMessageVariables column without having to specifically declare each replacement, so that a generic solution remains for N variables.

Thank you in advance.

Solution

I was finally able to fix it.
I built a fairly simple function that solves the need successfully.

The SQL function:

DELIMITER ;;

DROP FUNCTION IF EXISTS strTemplateReplace;;
CREATE FUNCTION strTemplateReplace(templateText text, replacementJSON text) RETURNS text CHARSET utf8
BEGIN
    DECLARE replacementKeys TEXT;
    DECLARE replacementQty INT UNSIGNED DEFAULT 0;
    DECLARE resultText TEXT;
    DECLARE counterVar INT UNSIGNED DEFAULT 0;
    DECLARE currentKey TEXT;
    DECLARE currentValue TEXT;
    SET replacementKeys = JSON_KEYS(replacementJSON);
    SET replacementQty = JSON_LENGTH(replacementJSON);
    SET resultText = templateText;

    WHILE counterVar < replacementQty DO
        SET currentKey = JSON_UNQUOTE(
            JSON_EXTRACT(
                replacementKeys,
                CONCAT('$[', counterVar, ']')
            )
        );
        SET currentValue = JSON_UNQUOTE(
            JSON_EXTRACT(
                replacementJSON,
                CONCAT('$.', currentKey)
            )
        );
        SET resultText = REPLACE(
            resultText,
            currentKey,
            currentValue
        );
        SET counterVar = counterVar + 1;
    END WHILE;

    RETURN ( resultText );
END;;

DELIMITER ;

The result is as expected:

MariaDB [test_db]> SELECT id, textMessage, textMessageVariables, strTemplateReplace(textMessage, textMessageVariables) AS replacementResult FROM events_log LIMIT 3;
+----+-------------------------------+----------------------+------------------------+
| id | textMessage                   | textMessageVariables | replacementResult      |
+----+-------------------------------+----------------------+------------------------+
|  1 | Data added (#%referenceCode%) | {"%referenceCode%":1}       | Data added (#1) |
|  2 | Data added (#%referenceCode%) | {"%referenceCode%":2}       | Data added (#2) |
|  3 | Data added (#%referenceCode%) | {"%referenceCode%":3}       | Data added (#3) |
+----+-------------------------------+----------------------+------------------------+
3 rows in set (0.002 sec)

Note: Works perfectly in the 10.5.13-MariaDB-1:10.5.13+maria~focal environment.

Answered By – Thomas Aquinas

Answer Checked By – Cary Denson (BugsFixing Admin)