This example shows how to overcome what seems to be commonplace: JSON Web Services that return an invalid JSON document.

JSON_TABLE and survival tips for shredding JSON with SQL

-- This fails to return data....why?
SELECT cusip, issueDate, bidToCoverRatio
    FROM JSON_TABLE(
      SYSTOOLS.HTTPGETCLOB('https://www.treasurydirect.gov/TA_WS/securities/announced?format=json&type=FRN&pagesize=5', null),
      '$.root[*]'   
      COLUMNS(cusip VARCHAR(10)      PATH '$.cusip',
              issueDate Timestamp    PATH '$.issueDate',
              bidToCoverRatio double PATH '$.bidToCoverRatio') 
    ) AS X;
stop;
   
-- Add ERROR ON ERROR to insure that there isn't a failure
SELECT cusip, issueDate, bidToCoverRatio
    FROM JSON_TABLE(
      SYSTOOLS.HTTPGETCLOB('https://www.treasurydirect.gov/TA_WS/securities/announced?format=json&type=FRN&pagesize=5', null),
      '$.root[*]'   
      COLUMNS(cusip VARCHAR(10)      PATH '$.cusip',
              issueDate Timestamp    PATH '$.issueDate',
              bidToCoverRatio double PATH '$.bidToCoverRatio') ERROR ON ERROR
    ) AS X;
    
stop;

-- AHA moment.... the JSON is invalid!
-- Why?
-- Valid JSON documents are of the form { key : value }
-- When a JSON document is published as [{ key : value }, { key : value }, ...}
-- a trick can be used to transform the JSON into a JSON document

select cusip, issuedate, bidtocoverratio
  from json_table ('{ "root" : ' concat
      systools.httpgetclob('https://www.treasurydirect.gov/TA_WS/securities/announced?format=json&type=FRN&pagesize=5', null) concat ' } ', 
      '$.root[*]' 
      columns (cusip     varchar(10)  path '$.cusip',
               issuedate timestamp    path '$.issueDate', 
               bidtocoverratio double path '$.bidToCoverRatio') error on error) as x;

stop;

-- Example output: 
-- CUSIP       ISSUEDATE                   BIDTOCOVERRATIO
-----------------------------------------------------------
-- 912828YN4	2019-10-31 00:00:00.000000	2.58
-- 9128287G9	2019-09-27 00:00:00.000000	2.66
-- 9128287G9	2019-08-30 00:00:00.000000	2.92
-- 9128287G9	2019-07-31 00:00:00.000000	2.73
9128286Q8	2019-06-28 00:00:00.000000	3.36