SQL on US Census - Null values return

Question

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;

Input file

Answer 1

Please try the following solution.

Your JSON is a JSON array, so it needs a slightly different syntax.

SQL

DECLARE @JSON VARCHAR(MAX);

SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Users\amartinez\US.json', SINGLE_CLOB) j;

SELECT tradeF.* 
FROM OPENJSON (@JSON)
WITH  (
   [CTY_CODE] varchar(max) '$[0]',  
   [CTY_NAME] varchar(max) '$[1]',  
   [I_ENDUSE] varchar(max) '$[2]',  
   [I_ENDUSE_LDESC] varchar(max) '$[3]', 
   [GEN_VAL_MO] varchar(max) '$[4]',
   [CON_VAL_MO] varchar(max) '$[5]',
   [time] varchar(max) '$[6]'
   ) as tradeF;