"where" on json field causing Arithmetic overflow error

Question

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

Martin Smith · Accepted Answer · 2016-09-04 17:39:50Z

5

This isn't anything to do with JSON specifically. You see the same with

SELECT 1
WHERE  '123' > 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)) > 12.0

Would work fine for your example data.

edited Sep 4, 2016 at 17:39

answered Sep 4, 2016 at 15:27

Martin Smith

88.7k15 gold badges262 silver badges366 bronze badges

So really the problem is that whatever i compare it to sets the implicit cast?

Amir
– Amir

2016-09-04 15:33:08 +00:00
Commented Sep 4, 2016 at 15:33
@Amir - yes. As long as whatever you are comparing it to has a higher precedence than nvarchar. If you use 12 then that is int so you don't get the issue either.

Martin Smith
– Martin Smith

2016-09-04 15:34:55 +00:00
Commented Sep 4, 2016 at 15:34
Sorry for the messy question, when you edited it did you use tags I dont know or did you do it manually? I used <code></code>

Amir
– Amir

2016-09-04 15:37:42 +00:00
Commented Sep 4, 2016 at 15:37
Don't use those tags. Just indent the content you want formatted as code. You can select the desired text and then use the {} button in the editor toollbar,

Martin Smith
– Martin Smith

2016-09-04 15:39:07 +00:00
Commented Sep 4, 2016 at 15:39

Add a comment |

Stack Exchange Network

"where" on json field causing Arithmetic overflow error

1 Answer 1

Your Answer

Hot Network Questions

"where" on json field causing Arithmetic overflow error

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions