How return different type values in MySQL case statement-CodePudding

I'm trying to cast string value to decimal while creating JSON_OBJECT like this

select
JSON_OBJECT('value',
case 1
when 1 then CAST('1' as decimal)
when 2 then '2'
end)

And result looks like

{"value": "1"}

but I expected

{"value": 1}

If second when statement return decimal value like when 2 then 2 result is correct, but I need to return different types from case statement, decimal in first case and string in second one like

{"value": 1}
{"value": "2"}

So how should I modify my query to get expected result?

mysql version is 8.0

CodePudding user response：

You can move the JSON_OBJECT generation inside the CASE construct as follows:

SELECT CASE 1 WHEN 1 THEN JSON_OBJECT('value', CAST('1' AS DECIMAL))
              WHEN 2 THEN JSON_OBJECT('value', '2')
       END

Check the demo here.