Search code examples
sqlreplaceutf-8character-encodingmariadb

How to deal with JSON encoded string in MySQL REPLACE Statement

I have this string in some MariaDB Column (let's called it MY_COL): {"libelle": "com\u00e9dien"} that i'd like to replace it with {"libelle": "acteur"}

Using this STO post, I figured out how to select rows containing this string:

SELECT MY_COL
FROM MY_TABLE
WHERE MY_COL LIKE '%{"libelle": "com\\\\u00e9dien%'

But, when I used this "over-sized escaping" technic in an UPDATE Statement, it doesn't work

UPDATE MY_TABLE
SET MY_COL = REPLACE(MY_COL, '{"libelle": "com\\\\\\u00e9dien"', '{"libelle": "acteur"')

Nevertheless, I noticed that this SELECT statement works well:

SELECT '{"libelle": "com\\\\\\u00e9dien"' As `Column_Before`,
REPLACE('{"libelle": "com\\\\\\u00e9dien"', '{"libelle": "com\\\\\\u00e9dien"', '{"libelle": "acteur"') As `Column_After`;

enter image description here

It seems that referring to the Column_Name in the REPLACE statement leads to this issue but I don't know why nor do I know how to prevent that.

Last info: the table, the column and the database use UTF8MB4 encoding with utf8mb4_general_ci collation.

I use DBeaver (latest version) and MariaDB's version is 10.8.6

Solution

  • You should use the proper JSON functions

    UPDATE MY_TABLE
SET MY_COL = JSON_SET(MY_COL, '$.libelle', 'acteur')
WHERE JSON_VALUE(MY_COL, '$.libelle') = 'comédien';

    If you have an array of such objects you can unnest them in a subquery, change what needs to be changed and reaggregate.

    UPDATE MY_TABLE
SET MY_COL = (
    SELECT JSON_ARRAYAGG(
      JSON_OBJECT('libelle',
        CASE WHEN j.libelle = 'comédien'
             THEN 'acteur'
             ELSE j.libelle
             END
        )
      )
    FROM JSON_TABLE(MY_COL, '$[0]' columns (
        libelle varchar(100) path '$.libelle'
    )) AS j
);