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