The following shows that the same (JSON) table data and the same queries yield different results when different NLS_NUMERIC_CHARACTERS are used.
The above sentence is no surprise, obviously, but in this case I consider the behavior to be wrong; Please explain why it isn't.

Prepare the data

drop table j_nls;

CREATE TABLE j_nls(
  id          NUMBER(10),
  jsondoc     CLOB CONSTRAINT ensure_json CHECK (jsondoc IS JSON)
);


INSERT INTO j_nls VALUES(
160000,
'{ "IDNumber"      : 160000,
   "Description"   : "Just a wide ID",
   "DecimalNumber" : 160000}'
);

INSERT INTO j_nls VALUES(
1601,
'{ "IDNumber"      : 1601,
   "Description"   : "Simple Integer",
   "DecimalNumber" : 1601}'
);

INSERT INTO j_nls VALUES(
1602,
'{ "IDNumber"      : 1602,
   "Description"   : "One decimal",
   "DecimalNumber" : 1602.1}'
);

INSERT INTO j_nls VALUES(
1603,
'{ "IDNumber"      : 1603,
   "Description"   : "Three decimals",
   "DecimalNumber" : 1603.123}'
);

COMMIT;

Set NLS_NUMERIC_CHARACTERS to the "European" settings: I want to see a decimal comma in my results.

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";

Use simple dot notation to look at the JSON data:

select j.id, j.jsondoc.IDNumber, j.jsondoc.Description, j.jsondoc.DecimalNumber from j_nls j;

=> All JSON fields as string (left aligned, in SQL developer). OK.

Use JSON_VALUE() to look at the JSON data:

SELECT id,
       json_value(jsondoc, '$.IDNumber')      AS J_ID,
       json_value(jsondoc, '$.Description')   AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber') AS J_DECIMAL
FROM j_nls;

=> All JSON fields as string (left aligned, in SQL developer). OK.

Use JSON_VALUE() to return one field (that has no decimal numbers) as a number.

SELECT id,
       json_value(jsondoc, '$.IDNumber' RETURNING NUMBER) AS J_ID,
       json_value(jsondoc, '$.Description')               AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber')             AS J_DECIMAL
FROM j_nls;

=> J_ID is right aligned (in SQL developer) => it is indeed a number. OK.

Use JSON_VALUE() to return the DecimalNumber field/key/column as numbers.

SELECT id,
       json_value(jsondoc, '$.IDNumber' RETURNING NUMBER)      AS J_ID,
       json_value(jsondoc, '$.Description')                    AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber' RETURNING NUMBER) AS J_DECIMAL
FROM j_nls;

Hmm... J_DECIMAL is null for "One decimal" and "Three decimals"
=> not the correct result :(

Try to do some calculations

with src as (
  SELECT id,
         json_value(jsondoc, '$.IDNumber' RETURNING NUMBER)      AS J_ID,
         json_value(jsondoc, '$.Description')                    AS J_DESC,
         json_value(jsondoc, '$.DecimalNumber' RETURNING NUMBER) AS J_DECIMAL
  FROM j_nls)
select ID, J_ID, J_DESC, J_DECIMAL, J_DECIMAL/2, J_DECIMAL*1000 from src;

=> So both non-integer values are replaced by SQL NULL values :(


Let's try JSON_TABLE() instead of JSON_VALUE(), returning everything as strings:

SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      VARCHAR2(300 CHAR) PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL VARCHAR2(300 CHAR) PATH '$.DecimalNumber'
     )) jt;

=> All JSON fields as string (left aligned). OK.

JSON_TABLE with the first, non-decimal numeric column, just like before

SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      NUMBER(10)         PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL VARCHAR2(300 CHAR) PATH '$.DecimalNumber'
     )) jt;

=> J_ID is right aligned (in SQL Developer) => it is indeed a number

And now using JSON_TABLE() to return the DeciamlNumber as a number:

SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      NUMBER(10)         PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL NUMBER(10,6)       PATH '$.DecimalNumber'
     )) jt;

=> ORA-01722: invalid number !!!!!!!



Now let's do the same queries, but after changing NLS_NUMERIC_CHARACTERS to the american format.

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,";

Use simple dot notation to look at the JSON data:

select j.id, j.jsondoc.IDNumber, j.jsondoc.Description, j.jsondoc.DecimalNumber from j_nls j;

=> All JSON fields as string (left aligned, in SQL developer). OK.

Use JSON_VALUE() to look at the JSON data:

SELECT id,
       json_value(jsondoc, '$.IDNumber')      AS J_ID,
       json_value(jsondoc, '$.Description')   AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber') AS J_DECIMAL
FROM j_nls;

=> All JSON fields as string (left aligned, in SQL developer). OK.

Use JSON_VALUE() to return one field (that has no decimal numbers) as a number.

SELECT id,
       json_value(jsondoc, '$.IDNumber' RETURNING NUMBER) AS J_ID,
       json_value(jsondoc, '$.Description')               AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber')             AS J_DECIMAL
FROM j_nls;

=> J_ID is right aligned (in SQL developer) => it is indeed a number. OK.

Use JSON_VALUE() to return the DecimalNumber field/key/column as numbers.

SELECT id,
       json_value(jsondoc, '$.IDNumber' RETURNING NUMBER)      AS J_ID,
       json_value(jsondoc, '$.Description')                    AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber' RETURNING NUMBER) AS J_DECIMAL
FROM j_nls;

Hmm... now everything seems correct, all of a sudden :)
(Except that the numbers are displayed with a decimal dot instead of a decimal comma, what I really want.)

Try to do some calculations

with src as (
  SELECT id,
         json_value(jsondoc, '$.IDNumber' RETURNING NUMBER)      AS J_ID,
         json_value(jsondoc, '$.Description')                    AS J_DESC,
         json_value(jsondoc, '$.DecimalNumber' RETURNING NUMBER) AS J_DECIMAL
  FROM j_nls)
select ID, J_ID, J_DESC, J_DECIMAL, J_DECIMAL/2, J_DECIMAL*1000 from src;

=> All values are there, all calculations return expected results :)
(Except that the numbers are displayed with a decimal dot instead of a decimal comma, what I really want.)


Let's try JSON_TABLE() instead of JSON_VALUE(), returning everything as strings:

SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      VARCHAR2(300 CHAR) PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL VARCHAR2(300 CHAR) PATH '$.DecimalNumber'
     )) jt;

=> All JSON fields as string (left aligned). OK.

JSON_TABLE with the first, non-decimal numeric column, just like before

SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      NUMBER(10)         PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL VARCHAR2(300 CHAR) PATH '$.DecimalNumber'
     )) jt;

=> J_ID is right aligned (in SQL Developer) => it is indeed a number

And now using JSON_TABLE() to return the DeciamlNumber as a number:

SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      NUMBER(10)         PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL NUMBER(10,6)       PATH '$.DecimalNumber'
     )) jt;

=> With these NLS settings there is no error :)




It is clear that with NLS_NUMERIC_CHARACTERS = ".," the results are correct:
The JSON standard describes the possibility for decimal numbers as scalar values, but apart from the digits, only the dot is allowed (as decimal separator). (Exponential notation is also allowed, but not mentioned nor used here.)
JSON data is always stored as a string (into a VARCHAR2, CLOB or BLOB), so NLS_NUMERIC_CHARACTERS has absolutely no influence when entering JSON data. Good.
Problems may arise when using the RETURNING NUMBER clause of the JSON_VALUE()-function. Instead of using the normal TO_NUMBER()-function (which honors NLS_NUMERIC_CHARACTERS), it should use a version specific for JSON data, independent of the NLS-settings. The reason for that is that the JSON standard defines what non-digit character(s) can be used to write numeric values -- only the dot -- and what its function is -- decimal separator -- (see http://www.json.org/number.gif )
The consequence of using the classic TO_NUMBER (and thereby honouring NLS_NUMERIC_CHARACTERS) is even worse when using JSON_TABLE() and defining NUMBER-columns: then it could happen that no data is returned at all.

I want to be able to use JSON_VALUE() and or JSON_TABLE() to return JSON numeric values as numbers. The resulting "fields" should act like any other numeric field in Oracle: When displaying them they must follow my preferred NLS_NUMERIC_CHARACTERS settings. When somebody else displays the same data using the same queries the numbers displayed should be according to their NLS_NUMERIC_CHARACTERS settings which may or may not be equal to mine.