I am using APEX_WEB_SERVICE.Make_Rest_Request to get a JSON document from a vendor API. Unfortunately, the JSON I an getting back has two issues - many of the double quotes (") are escaped, and the bulk of the result is contained in a JSON array, which is also quoted. These issues seem to annoying the PL/SQL parser, as well as some other JSON tools. A (condensed sample):
[{"FMSC_Results":"[{\"DATEADDED\":\"Apr 1 2025 7:35PM\",\"GROUP_\":\"TestTest\"},
{\"DATEADDED\":\"Mar 21 2025 9:26AM\",\"GROUP_\":\"\"}]"}]
I don't know if I can get the vendor to give me “cleaner” JSON - In order to query their database, I need to call a stored procedure via their REST interface. I get the feeling that these stored procedures are custom written for each table for each customer)
My current approach is to write the clob I get Make_Rest_Request to the filesystem, run it through “sed”, and read it back in and parse it, but there must be a better way.