Search code examples
mysqljsoncasting

How do I cast a JSON sub-value to an integer in MySQL?

I currently have this

  UPDATE {$table}
            SET
                {$column} = JSON_SET(
                    {$column},
                    '$.{$jsonKey}',
                    {$column}->>'$.{$jsonKey}' * 100
                )

However the value inside the json will be a double. I would like it to be an integer. The code below results in a syntax error.

 UPDATE {$table}
            SET
                {$column} = JSON_SET(
                    {$column},
                    '$.{$jsonKey}',
                    {$column}->> CAST('$.{$jsonKey}' * 100 AS UNSIGNED)
                )
Solution

  • You can combine JSON_EXTRACT with JSON_SET, and then CAST the extracted value to Integer.

    UPDATE {$table}
SET
    {$column} = JSON_SET(
        {$column},
        '$.{$jsonKey}',
        CAST(JSON_EXTRACT({$column}, '$.{$jsonKey}') * 100 AS UNSIGNED)
    )