Stevie Zakhour

asked on 2/2/2020

Import Json file into MS SQL

Hi Guys

Trying to import a json formatted file into sql, the query is below

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Users\szakhour\Desktop\LastScore.json', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON)
WITH 
(
    LeaderboardName varchar(20), 
    LeaderboardNameFull varchar(20), 
    PlayerCount varchar(20), 
    PlayerName varchar(20), 
    Score nvarchar(20), 
    ScoreDetails varchar(10)
)
CROSS APPLY OPENJSON (ScoreDetails) 
WITH(
		ZombiesKilled varchar(15)'$.ZombiesKilled',
		Accuracy varchar(50)'$.Accuracy',
		DamageTaken varchar(20)'$.DamageTaken',
		SecondsInLevel varchar(30)'$.SecondsInLevel',
		Deaths varchar(50)'$.Deaths' --AS JSON
)

Open in new window

The json formatted file is below

{
  "LeaderboardName": "Missile base",
  "LeaderboardNameFull": "Missile base four players",
  "PlayerCount": 4,
  "PlayerName": "PLAYER",
  "Score": 35360,
  "ScoreDetails": {
    "ZombiesKilled": "335",
    "Accuracy": "31.32",
    "DamageTaken": "100",
    "SecondsInLevel": "1273",
    "Deaths": "1"
  }
}

Open in new window

When I run the query I get no errors however there is no data. See attached png file called Snip20200203_3.Png, this will show you the result after running the query in MS SQL 2016

Any help is greatly appreciated.
Snip20200203_3.png

JSONSQL* import

Last Comment

Stevie Zakhour

8/22/2022 - Mon

Ryan Chong

2/2/2020

you can simply try this:

SELECT a.*, b.*
FROM OPENJSON (@JSON)
WITH 
(
    LeaderboardName varchar(20), 
    LeaderboardNameFull varchar(20), 
    PlayerCount varchar(20), 
    PlayerName varchar(20), 
    Score nvarchar(20)
) a
CROSS APPLY OPENJSON (@JSON) 
WITH(
        ZombiesKilled varchar(15)'$.ScoreDetails.ZombiesKilled',
        Accuracy varchar(50)'$.ScoreDetails.Accuracy',
        DamageTaken varchar(20)'$.ScoreDetails.DamageTaken',
        SecondsInLevel varchar(30)'$.ScoreDetails.SecondsInLevel',
        Deaths varchar(50)'$.ScoreDetails.Deaths'
) b

Open in new window

ASKER CERTIFIED SOLUTION

Ryan Chong

2/2/2020

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

Stevie Zakhour

2/2/2020

ASKER

Thanks Ryan - do you know if there is a way to bulk upload any .json file, i.e.

select @JSON = BulkColumn from openrowset (bulk 'C:\Users\szakhour\Desktop\Freak\*.json', single_clob) as j

Ryan Chong

2/2/2020

select @JSON = BulkColumn from openrowset (bulk 'C:\Users\szakhour\Desktop\Freak\*.json', single_clob) as j

That should worked as well.

Example below worked for me:

Declare @JSON varchar(max)

select @JSON = BulkColumn from openrowset (bulk 'D:\Ryan\temp\test.json', single_clob) as j 

SELECT *
FROM OPENJSON (@JSON)
WITH 
(
    LeaderboardName varchar(20), 
    LeaderboardNameFull varchar(20), 
    PlayerCount varchar(20), 
    PlayerName varchar(20), 
    Score nvarchar(20),
    ZombiesKilled varchar(15)'$.ScoreDetails.ZombiesKilled',
    Accuracy varchar(50)'$.ScoreDetails.Accuracy',
    DamageTaken varchar(20)'$.ScoreDetails.DamageTaken',
    SecondsInLevel varchar(30)'$.ScoreDetails.SecondsInLevel',
    Deaths varchar(50)'$.ScoreDetails.Deaths'
)

Open in new window

Where test.json got the exact content as what you have shared.

Your help has saved me hundreds of hours of internet surfing.

fblack61

Stevie Zakhour

2/2/2020

ASKER

Thanks Ryan, I don't think that would work for a batch import. For example, I have a folder in directory C:\Users\szakhour\Desktop\Freak.

In this Freak folder, there is 2 .json files, see below

What I want to do is import whatever .json file comes into the Freak folder into SQL whenever the script is run. I hope this makes sense. If not let me know

Ryan Chong

2/2/2020

What I want to do is import whatever .json file comes into the Freak folder into SQL whenever the script is run. I hope this makes sense. If not let me know

I see... if that's the case, probably we need to do some looping and load the data files accordingly.

some possible approaches are SSIS, self-customized ETL programs, etc.

If you want to discuss this further in your next question?

Stevie Zakhour

2/2/2020

ASKER

Thank Ryan, I’ll raise another question then. Thank you for your help

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.