Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save forstie/b45fd2cd06e5103aee6fe52d63f9f628 to your computer and use it in GitHub Desktop.
Save forstie/b45fd2cd06e5103aee6fe52d63f9f628 to your computer and use it in GitHub Desktop.
This example shows how to overcome what seems to be commonplace: JSON Web Services that return an invalid JSON document.
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment