"where" on json field causing Arithmetic overflow error I have a table:

when I select all from the table:

 SELECT JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') as x FROM
(VALUES    
(2,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":12}},"customId":null}'),
(3,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":32}},"customId":null}'),
(4,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":22}},"customId": null}'),
(5,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value": 12.0},"7-temp": {"name": "Temperature","value":123}},"customId": null}')    
)
[AmirTestTable](Id,LastReadings )


I get 

+-----+
|  x  |
+-----+
|  12 |
|  32 |
|  22 |
| 123 |
+-----+


However when i try to add a where clause:

 SELECT JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') as x 
 FROM
(VALUES
  (2,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":12}},"customId":null}'),
  (3,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":32}},"customId":null}'),
  (4,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":22}},"customId": null}'),
  (5,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value": 12.0},"7-temp": {"name": "Temperature","value":123}},"customId": null}')
)
[AmirTestTable](Id,LastReadings )
where JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') &gt; 12.0


I get the error:


  Arithmetic overflow error converting nvarchar to data type numeric.


If I change the 123 value to 99 the query works, or if i change the where clause to > 12. It also works if i cast it explicitly to a number.

I don't understand why it doesn't work directly though.
This isn't anything to do with JSON specifically. You see the same with

SELECT 1
WHERE  '123' &gt; 12.0;


JSON_VALUE has a return type of nvarchar(4000).

The literal 12.0 has a datatype of numeric(3,1).

As numeric/decimal has a higher datatype precedence than nvarchar the result of the function call is cast to numeric(3,1)



You can use an explicit cast to control the scale and precision used yourself.

e.g.

 CAST(JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') AS NUMERIC(4,1)) &gt; 12.0


Would work fine for your example data.