How to convert integer value to float in json column in MariaDB?

June 1, 2023

I need to convert values of $.fonts_size json property to float.

Currently there are integer values such as 1 and I need them to become 1.0 to make my app treat them as double.

In this case json like:

{
    "fonts_size": 1
}

Should be converted to:

{
    "fonts_size": 1.0
}

Is that possible to do within SQL in MariaDB?

I tried solutions like this:

SELECT JSON_EXTRACT(
    JSON_SET(
        settings,
        "$.fonts_size",
        CAST(
            JSON_EXTRACT(
                settings,
                "$.fonts_size"
            )
            AS FLOAT
        )
    ),
    '$.fonts_size'
) 
FROM blocks WHERE JSON_TYPE(JSON_EXTRACT(settings, "$.fonts_size")) = "INTEGER"

However this does not make a difference.
Also tried concatenating ".0" but this results in string instead of double

>Solution :

This is a way to do it by casting the integer into DECIMAL with one digit after the decimal point:

SELECT CAST(JSON_EXTRACT(
                settings,
                "$.fonts_size"
       ) AS DECIMAL(5,1)),
JSON_TYPE(JSON_EXTRACT(settings, "$.fonts_size"))
FROM blocks
WHERE JSON_TYPE(JSON_EXTRACT(settings, "$.fonts_size")) = "INTEGER"

DECIMAL(5,1) means a number having 5 digits altogether, with 1 of them to the right of the decimal point. (So, 4 left, 1 right.)