Question 303365 in Mysql Question posted in
The official MySQL documentation can be found here.

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": "comu00e9dien"} 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 refering to the Column_Name in the REPLACE statement leads to this issue but neither i don’t know why nor i don’t find how to prevent that…

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

Tags:

2

Answers


  1. 0

    backslash is an escape character in both JSON and SQL strings, so it must be escaped, try this >>

    Method 1:

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

    Method 2:

    UPDATE MY_TABLE
SET MY_COL = JSON_REPLACE(MY_COL, '$.libelle', 'acteur')
WHERE JSON_UNQUOTE(JSON_EXTRACT(MY_COL, '$.libelle')) = 'comédien';

    Method 3:

    UPDATE MY_TABLE

    SET MY_COL = REPLACE(MY_COL, '{"libelle": "com\\u00e9dien"}', '{"libelle": "acteur"}')
WHERE MY_COL LIKE '%com\\u00e9dien%';

    Method 4:

    UPDATE MY_TABLE
SET MY_COL = REPLACE(MY_COL, 'comédien', 'acteur')
WHERE MY_COL LIKE '%comédien%';
    Login or Signup to reply.
  2. 0

    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
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search