[SOLVED] How to convert json data type from integer to string from table using mysql

How to convert json data type from integer to string from table using mysql

My table contain thousand of rows and one of my column data in json format.I need to change json value integer to string.

For example: {"1":123} will changed to {"1":"123"}.

I have tried with below query.

UPDATE table
SET details = JSON_SET(details, '$."1"', CAST(JSON_UNQUOTE(JSON_EXTRACT(details, '$."1"')) AS CHAR))
WHERE JSON_UNQUOTE(JSON_EXTRACT(details, '$."1"')) IS NOT NULL;

But i got this error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))
WHERE JSON_UNQUOTE(JSON_EXTRACT(details, '$."1"')) IS NOT NULL' at line 2

Solution

UPDATE your_table_name
SET details = JSON_SET(
    details, 
    '$."1"', 
    CAST(JSON_UNQUOTE(JSON_EXTRACT(details, '$."1"')) AS CHAR)
)
WHERE JSON_UNQUOTE(JSON_EXTRACT(details, '$."1"')) IS NOT NULL;

The above query will update the details column in your table, converting the integer value within the JSON object to a string where it's not null.

Check Here for example.