How to return decimals in the right number format with Oracle JSON_VALUE

Question

The decimal separator in the JSON format is the period (.). However, when the number format of a system uses comma as a decimal separator (e.g. 10,3 instead of 10.3), the Oracle json_value expects that in order to return a valid number.

Example:

SELECT 
json_value(METADATA, '$.Amount' RETURNING NUMBER(18,6)) As AMOUNT 
FROM TABLE

This will return (null) for any JSON value that contains a decimal. I cannot save strict JSON with comma decimals. How can I then get a number back from valid JSON if Oracle cannot handle that conversion? Is there some keyword I can use in my RETURNING statement?

Note1: I have used to_number(Replace(...)) but that feels wrong and slows down some queries.

Note2: I do not want to change the number format in my session, since I want to parse the JSON and use it in a view.

Answer 1

You can use TO_NUMBER() without a REPLACE(). For example:

select to_number('12345,67', '9999999999D9999', 'NLS_NUMERIC_CHARACTERS='',.''')
from dual;

TO_NUMBER() documentation.

Answer 2

Comma decimals are not legal inside JSON data. For example: {"pi":3,1415} is not valid.

Therefore, JSON_VALUE input is invalid JSON (and a NULL ON ERROR handler kicks in).

If you see a NULL value then changing from the default to ERROR ON ERROR often helps. See the ERROR ON ERROR doc.

SELECT json_value(metadata, '$.Amount'
                  RETURNING NUMBER(18,6)
                  ERROR ON ERROR)
 AS amount FROM table

You can only pass valid JSON to JSON_VALUE. You need to replace the comma with a period (dot), and preferably before storing the JSON data.

If you use the SQL REPLACE operator then you can also replace commas that you want to keep.

Answer 3

To add to what Beda says, Oracle should process JSON without any sensitivity to the Locale setting.

This means the JSON text should use the decimal point and not the comma. In fact the comma cannot be supported as a radix character in JSON because it implies the next element in an array or the next key/value pair in an object.

If you see behaviour like that, it's a bug and should be reported.

Note that if you select a NUMBER value out of a JSON, it may be rendered (by sqlplus or another tool layer) using the comma, but this does not mean the underlying JSON has a comma as the radix character.

You can try:

select JSON_VALUE('{"a":1.234}', '$.a' returning NUMBER) from DUAL

to test this.