Using json_array in Oracle 12c:
SELECT
COLUMN_VALUE "value"
, json_array(COLUMN_VALUE) "json_output"
FROM TABLE(SYS.ODCINUMBERLIST(5.00, -5.00, 0.50, -0.50, 0.05, -0.05))
value json_output
5 [5]
-5 [-5]
0.5 [0.5] <--Zero before decimal
-0.5 [-0.5]
.05 [.05] <--No zero before decimal
-.05 [-.05]
The last two json_output strings, [.05] and [-.05], do not conform to RFC 4627 and fail validation at JSONlint and Code Beautify.
Parsing them on SQL Server 2017 fails:
SELECT * FROM OPENJSON('[.05]')
Msg 13609, Level 16, State 4, Line 137
JSON text is not properly formatted. Unexpected character '.' is found at position 1.
How can I force Oracle to produce RFC4627 compliant JSON? Alternatively, how can I get SQL Server to parse the noncompliant format?
EDIT: This is a large (for my resources) datastream, about 1TB/hour. I'm hoping for a cleaner solution than editing the JSON strings mid-flight.