Skip to main content
Database Administrators

Return to Answer

Cosmetic cleanup.
Source Link
edit approved Aug 31, 2021 at 20:00
Drew
edit approved Aug 31, 2021 at 20:00
Drew
  • 133
  • 5

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

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

If you see a NULLNULL value then changing from the default to ERROR ON ERRORERROR ON ERROR often helps

SELECT json_value(METADATA, '$.Amount' RETURNING NUMBER(18,6) ERROR ON ERROR) As AMOUNT FROM TABLE 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_VALUEJSON_VALUE. You need to replace the comma with a dot. Ideallyperiod (dot), and preferably before storing the JSON data. 

If you use the SQL REPLACEREPLACE operator then you maycan also replace commas that you want to keep.

comma decimals are not legal inside JSON data, for example {"pi":3,1415} is not valid. Therefore, JSON_VALUE input is invalid JSON (and NULL ON ERROR handler kicks in)

If you see a NULL value then changing the default to ERROR ON ERROR often helps

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 dot. Ideally, before storing the JSON data. If you use the SQL REPLACE operator you may also replace commas that you want to keep.

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.

Source Link
Beda Hammerschmidt
Beda Hammerschmidt
  • 109
  • 1

comma decimals are not legal inside JSON data, for example {"pi":3,1415} is not valid. Therefore, JSON_VALUE input is invalid JSON (and NULL ON ERROR handler kicks in)

If you see a NULL value then changing the default to ERROR ON ERROR often helps

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 dot. Ideally, before storing the JSON data. If you use the SQL REPLACE operator you may also replace commas that you want to keep.