I am trying to get data from the US Census on SQL Server Studio for analysis (trade data at port level). I have downloaded a JSON file for now from their API (ideally, I will do a call from SQL studio later). I then read the file with OPEN ROW SET and OPEN JSON, I can read the file but when I add the with clause to get the column, I only get NULL values.
Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Users\amartinez\US.json', SINGLE_CLOB) j
SELECT * FROM OPENJSON (@JSON)
WITH (
[CTY_CODE] varchar(max) '$.CTY_CODE',
[CTY_NAME] varchar(max) '$.CTY_NAME',
[I_ENDUSE] varchar(max) '$.I_ENDUSE',
[I_ENDUSE_LDESC] varchar(max) '$.I_ENDUSE_LDESC',
[GEN_VAL_MO] int '$.GEN_VAL_MO',
[CON_VAL_MO] int '$.CON_VAL_MO',
[time] varchar(max) '$.time'
) as tradeF;
Please try the following solution.
Your JSON is a JSON array, so it needs a slightly different syntax.
SQL