SELECT FOR JSON outputs exponent on floating points

Question

When I execute the following query:

DECLARE @KG FLOAT = 5.0;

WITH Weight(KG, LB) AS (SELECT @KG, @KG * 2.2046226218)
SELECT * FROM Weight;

I get this output:

| KG | LB           |
|----|--------------|
| 5  | 11.023113109 |

Now when I simply append FOR JSON AUTO to output JSON,

DECLARE @KG FLOAT = 5.0;
    
WITH Weight(KG, LB) AS (SELECT @KG, @KG * 2.2046226218)
SELECT * FROM Weight FOR JSON AUTO;

I get the following output:

[{"KG":5.000000000000000e+000,"LB":1.102311310900000e+001}]

I know this the above is valid JSON, but my desired output is:

[{"KG":5,"LB":11.023113109}]

Important note: I realize that I can get the desired output by simply declaring @KG as DECIMAL or INT. I'd rather not do that, since I'm selecting from a VIEW that I do not have permissions to edit, and I don't want to individually cast each FLOAT column to DECIMAL if possible.

Is there a way to do this without casting each individual value to DECIMAL?

Answer 1

You should use exact numeric datatypes:

DECLARE @KG DECIMAL(38,12) = 5.0;

WITH Weight(KG, LB) AS (SELECT @KG, @KG * 2.2046226218)
SELECT * FROM Weight FOR JSON AUTO;

DbFiddle Demo