如何将JSON列中的整数值转换为浮点数在MariaDB中?

huangapple go评论66阅读模式
英文:

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

问题

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

英文:

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

答案1

得分: 1

这是一种将整数转换为带有小数点后一位的DECIMAL的方法:

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) 表示一个数字总共有5位数,小数点右边有1位数(因此,左边有4位,右边有1位)。

英文:

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.)

huangapple
  • 本文由 发表于 2023年6月1日 15:00:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/76379398.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定