Why JSON import to a table shows all null values?

user_14611293 0
Viewed 207

I use SQL Server 2016 dev

I have tried to import JSON data into a SQL Server in-memory table before loading it to a physical table

DECLARE @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\somedatafolder\olympic-winners-large.json', SINGLE_CLOB) Q

SELECT * FROM OPENJSON (@JSON)

When the query executes, the result is shown as

enter image description here

Then I have load that data to an in-memory table

DECLARE @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\somedatafolder\olympic-winners-large.json', SINGLE_CLOB) AS Q

SELECT * FROM OPENJSON (@JSON) 
WITH ([Athlete] varchar(255),
      [Age] varchar(255),
      [Country] varchar(255)) AS sample1

After execution of the query it shown the correct number of rows were imported (8618) but all rows shown NULL value

enter image description here

I would like to know why this happened and how do I fix this?

tsql sql-server sql
1 Answers

JSON is case sensitive. Notice I changed the column names to lowercase

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\somedatafolder\olympic-winners-large.json', SINGLE_CLOB) AS Q
SELECT * FROM OPENJSON (@JSON) 
WITH(  [athlete] varchar(255),
       [age] varchar(255),
       [country] varchar(255) ) as sample1
user_15700000
Related